The following SQL statement will do what the Oracle ROUND(date,fmt)
function can not do, which is round a dateTime field to the nearest 10
seconds, PRESERVING THE DATE PART('dd-mon-yyyy') of the field.
Thanks to James' very helpful SQL statements (thanks James!) I derived
the following SQL:
SELECT
SYSDATE,TRUNC(SYSDATE,'MI')+((ROUND(SUBSTR(SYSDATE,19,2),-1))/86400)
FROM DUAL;
Notice: The date format of my SQL Editor is set to 'DD-MON-YYYY
HH24:MI:SS'
GL,
HAXIM0R
James Lorenzen <lorenzen+news_at_tiny.net> wrote in message news:<300120031027322699%lorenzen+news_at_tiny.net>...
> 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 Fri Feb 21 2003 - 19:06:30 CST
