I need to round this:
zc.solved_at_ts (FORMAT 'HH:MI') (CHAR(5)),
to the closest hour. ex if 16:35 then 17:00 and if 16:29 then 16:00
Round function did not work. Any ideas? It needs to be in one line nested something like this: round (zc.solved_at_ts (FORMAT 'HH:MI') (CHAR(5)))
Thanks so much in advance for the help.
ROUND always results in a DATE (blame Oracle), so you can't use it in that case.
What's the datatype of solved_at_ts and what datatype do you need as result? When you add 30 minutes you could simply extract the hour, e.g.
EXTRACT(HOUR FROM solved_at_ts + INTERVAL '30' MINUTE)
CASE WHEN EXTRACT(MINUTE FROM CURRENT_TIME(0)) - 30 >= 0
THEN CURRENT_TIME(0) + ((60 - EXTRACT(MINUTE FROM CURRENT_TIME(0))) * INTERVAL '1' MINUTE)
ELSE CURRENT_TIME(0) - EXTRACT(MINUTE FROM CURRENT_TIME(0)) * INTERVAL '1' MINUTE
END AS Converted_Time
,Converted_Time - TIME '00:00:00' HOUR TO MINUTE AS Rounded_Time;