Round up date field to next half hour in SQL

Question

In SQL, I have a DATE variable which I want to round to the next available half hour (i.e. xx:30). Note that this shouldn't just be to the nearest half hour.

Here are some examples:

Time                   Expected outcome
2012-03-26 11:25 AM    2012-03-26 11:30 AM
2012-03-26 11:45 AM    2012-03-26 12:30 PM

Is there a way to do this in SQL?

Answer 1

It's a bit convoluted, but something like

SELECT round( (date_col - trunc(date_col)) * 48 )/ 48 +
         trunc( date_col )
  FROM dual

should work.

SQL> ed
Wrote file afiedt.buf

  1  select round((sysdate - trunc(sysdate)) * 48)/48 + trunc(sysdate),
  2         sysdate
  3*   from dual
SQL> /

ROUND((SYSDATE-TRUNC SYSDATE
-------------------- --------------------
26-mar-2012 14:30:00 26-mar-2012 14:35:30

• TRUNC(date_col) truncates the time to midnight.
• date_col - trunc(date_col) returns the fraction of a day that has elapsed since midnight.
• Multiplying by 48 gives you the number of half-hour increments
• Rounding that gives you the nearest half-hour
• Then dividing by 48 gives you a fraction of a day that can be added back to the trunc(date_col)

Answer 2

Gave a +1 to Justin's answer for the originality.

If you want a more pedantic (or, perhaps more easily understood or maintained) aproach,

select case when to_number(to_char(sysdate,'mi')) > 30
       then trunc(sysdate,'mi') + ((60 - to_number(to_char(sysdate,'mi')) +30)/1440)
       else trunc(sysdate,'mi') + (30 - to_number(to_char(sysdate,'mi')))/1440 end
from dual

I'm only tossing this in there as this might be a more adaptable approach if you needed to do something similar against other numbers, like 45 minutes past the hour for example. You would just replace the three 30s with 45s and away you go.

For performance, though, for your exact problem - Justin's is a real winner.

Answer 3

Here's a little adjustment to Justin's answer that I think gives the results you want:

WITH thedate AS (SELECT trunc(SYSDATE)+6.5/48 thedate FROM dual)
select round((thedate - trunc(thedate)) * 24 - 1/(24*3600))/24 + trunc(thedate)+1/48,
          theDATE
     from thedate;

Basically, this rounds to the nearest hour (after subtracting a second to prevent rounding up when you are exactly on the half-hour) then adds a half hour.