CODEWITHSUNDEEP
sqloracle-databasedatetimetimestamp
Webb
Webb

Reputation: 139

ORACLE DATETIME ROUND UP WHEN MINUTES <> 0

I have a datetime:

select to_date(to_char(r.DATETIMEUTC,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as DATETIME_UTC,
       round(r.DATETIMEUTC, 'hh') as rounded
from   PROD.WX_RAW r;

I need to round up just when the minutes are different to 00.

Example:

1/1/2000 2:37:00 AM => should be => 1/1/2000 3:00:00 AM. (dont round up if hour =00) 1/1/2000 2:00:00 AM => should be => 1/1/2000 2:00:00 AM

For now my code has round function but this one look the nearest hour, I need even if minutes are 15, round up the hour, except when the minutes are 00 in that case I need to keep the hour.

Regards

Upvotes: 0

Views: 346

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

I'd truncate the date to the hour and then use a case statement to decide whether to add an hour.

trunc( r.DATETIMEUTC, 'HH24' ) -- truncate to the hour
+ case when to_number( to_char( r.DATETIMEUTC, 'HH24' ) ) != 0
       then interval '1' hour
       else null
    end

Upvotes: 1

user5683823
user5683823

Reputation:

Assuming datetimeutc is the input value, of date data type, you can do something like this:

select trunc(datetimeutc + interval '59' minute, 'hh')
from   .....

The math is pretty simple, but most programmers won't trust the math anyway; they will use the formula on a few examples to convince themselves it's correct. So I won't bother with the formal proof.

Upvotes: 1

Related Questions