All Forums Database
16 Sep 2014
TIme Difference

Hello All,
THe below query is to find the difference between Previous Hour Time - Minutes to get the beginning of the previous hour.
SEL CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI') AS CHAR(8)) - EXTRACT(MINUTE FROM(CAST(CAST(CURRENT_TIME AS FORMAT 'MI:SS') AS CHAR(5)))) (TITLE 'OUTPUT')
 
I am getting the output in integers as given below
OUTPUT
1,600.00
I want the output as 16:00.
 
Like DATE Function saved in integer internally, is time also saved in integer?
Thanks
Jugal
 

dnoeth 4628 posts Joined 11/04
16 Sep 2014

Hi Jugal,
you cast current_time to a CHARs and then substract two CHARs, what do you expect?
 
If you want the result to be a CHAR:

CAST(CAST(CURRENT_TIME AS FORMAT 'hh:') AS CHAR(3)) || '00'

If you want a TIME:

TIME '00:00:00' + (INTERVAL '01' HOUR * EXTRACT(HOUR FROM CURRENT_TIME))

 

Dieter

16 Sep 2014

Thanks Dieter.. :).. 

You must sign in to leave a comment.