Re: Rounding a Date field in Oracle

And right you are. I read this and went off to different machine to try it out with Oracle. In the process, I remembered the "round" request and failed to remember the "up" part. :-( This can be easily fixed by replacing "ROUND" with "CEIL".

Thanks for catching this.

Note: A previous post used the "TO_CHAR(date_fld,'sssss')" which is more elegant (shorter) solution.

James

In article <25fb645f.0301300026.3bc3284d_at_posting.google.com>, Kenneth Koenraadt <plovmand_at_mail-online.dk> wrote:

> 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-sub
> > > str(
> > > 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 - 10:27:32 CST