Re: Rounding Time

datexpr-trunc(datexpr) will get you the time component, as a fraction of a day. multiply that by 24, to get hours and fractional hours, then round that to integer hours, then divide by 24 to get it back to a fraction of a day...

like this...

trunc(L.LOGIN_DATE) +
  (round((L.LOGIN_DATE-trunc(L.LOGIN_DATE))*24))/24

the result of the expression is a DATE value... so wrap the whole expression in a TO_CHAR function to format it like you would any other DATE expression.

HTH <louster_at_my-deja.com> wrote in message
news:8kfapg$ka5$1_at_nnrp1.deja.com...
> I'm trying to round a time to the nearest hour on a 24 hr
 clock. I'm
> currently trying something like:
>
> TO_CHAR(L.LOGIN_DATE,'fmHH24')+(TRUNC(TO_CHAR
> (L.LOGIN_DATE,'fmMI')/30)) || ':00' as "Rounded"
>
> But the problem with this is 11:31pm rounds up to 24:00 but
 1:29am
> rounds to 0:00. I can't have both 24:00 and 0:00. I'd like
 11:31pm to
> round to 0:00. Any ideas?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Tue Jul 11 2000 - 00:00:00 CDT