Re: Rounding a Date field in Oracle

That *does not work* the way that was asked for :

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 ;

gives correctly 10:45:20, but

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

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

Gives 10:45:10,

which is *WRONG*, because 10:45:12 was supposed to be rounded *up* to 10:45:20, which is clearly stated in the original question.  

• Kenneth Koenraadt

James Lorenzen <lorenzen+news_at_tiny.net> wrote in message news:<290120031542400616%lorenzen+news_at_tiny.net>...
> 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 Thu Jan 30 2003 - 02:26:17 CST