Re: Rounding a Date field in Oracle

Here is one method that does not use a decode, but it does play games with the date functionality.

The code (using "dual") is:

select TO_CHAR(TRUNC(SYSDATE,'yyyy') +

         (ROUND((TO_DATE('10:45:15','hh:mi:ss') - 
         TRUNC(SYSDATE,'yyyy'))*8640)/8640),'hh:mi:ss') from dual ;

The TRUNC(sysdate,'yyyy') is first subtracted from the targeted time to convert it to a number. Since there are 86,400 seconds in a day and we care to round to the nearest ten second range, we multiple the resulting number by 8,640 (the number of ten second groups in a day.   

This number is ROUNDED to get to the desired rounding to the nearest ten seconds.

This is then divided by 8,640 to put it back as a number we can "add" to TRUNC(sysdate,'yyyy') to convert the mess back into a date.

Please note that the TRUNC(sysdate,'yyyy') is completely arbitrary and any date may possibly be used. That I haven't tried.

HTH
  James

In article <3e377739.1797484_at_news.inet.tele.dk>, Kenneth Koenraadt wrote:

> Hi Jim,
>
> Guess there are several more or less elegant ways to do it.
> Here's one:
>
> select
>
> date_field+to_number(decode(substr(to_char(date_field,'ss'),-1),0,0,10-substr(
> to_char(date_field,'ss'),-1))/60/60/24)
> from my_table;
>
>
> I suppose you did not want 10:15:00 to be rounded up to 10:15:10, but
> just to keep it 10:15:00. Thus the decode.
>
> - Kenneth Koenraadt
>
>
>
> On Wed, 29 Jan 2003 16:00:03 +1100, "JK" <jim.katsos_at_oz.quest.com>
> wrote:
>
> >Does anyone know a way to round a date field as shown below
> >
> >10:15:45 = 10:15:50
> >10:15:53 = 10:16:00
> >
> >basically round up to the nearest 10 second peroid to use in a group by
> >select. The round function only allow you to round to the nearest minute.
> >
> >
> >
> >
Received on Wed Jan 29 2003 - 15:42:40 CST