This content has been marked as final. Show 6 replies
will give you the hour for the sysdate.
I am trying to get the first hour of sysdate for eg: 11/13/2012 01:00:00 AM and set this value to my variable.
This below exp gives you the required value1 person found this helpful
replace(cast(month(current_date) as char)||'/'||cast(day(Time.Date) as char)||'/'||cast(year(current_date) as char),' ','')||' 01:00:00 AM'
As per your question I think this is answered ;) pls mark if it does
Edited by: veeravalli on Nov 13, 2012 1:17 PM
The below Jay's good to go
Edited by: veeravalli on Nov 13, 2012 1:19 PM
Thank You for your reply. I am able to produce the date, but I am having problems with date formatting issue. I need to use this value in the timestampdiff function. Basically, I want to set the, 2nd date in timestampdiff to 'sysdate + 1:00am' (1:00 AM of the current day).
How do I do this?
Hi,1 person found this helpful
TIMESTAMPADD(SQL_TSI_HOUR, 1, cast(cast(CURRENT_DATE as char) as timestamp))
Hope this will help you to get the sysdate+1:00 AM
And to add it to the timestampdiff:
TIMESTAMPDIFF(SQL_TSI_HOUR, CURRENT_TIMESTAMP, (TIMESTAMPADD(SQL_TSI_HOUR, 1, cast(cast(CURRENT_DATE as char) as timestamp))))
Edited by: Voltaire on Nov 13, 2012 11:19 AM
Thanks Jay. I was doing that but the result I had was not correct to the decimal place (I compared this result to the result of raw data). So, I just cast the current_date as char first and then appended 01:00 am and then converted the entire thing to timestamp. This works to the decimal point.