Re: Rounding a Date field in Oracle

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 - 00:48:31 CST