Re: Rounding Time

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/07/12
Message-ID: <38uoms0iua4r8l2ejpfl4mfussj1ak9i7v@4ax.com>#1/1

I think you would have better luck using the ROUND function as follows:

SQL> alter session set nls_date_format = 'dd-Mon-yyyy hh:mi:ss';

Session altered.

SQL> select round(sysdate,'hh') from dual;

ROUND(SYSDATE,'HH')

---

12-Jul-2000 10:00:00

ROUND(somedate,'HH') rounds a date to the nearest hour. If the minutes are > 30, it will round up. You can use TRUNC(somedate,'HH') if you always want to round down.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Tue, 11 Jul 2000 14:26:14 GMT, louster_at_my-deja.com wrote:

>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 Wed Jul 12 2000 - 00:00:00 CDT