Re: Rounding a Date field in Oracle

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 29 Jan 2003 22:57:41 +0100
Message-ID: <3ohg3vccjffk582cbj34lfinrl7pd1ou3t@4ax.com>

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.
>
>
>

trunc(date_field) +
(ceil (to_number(to_char(date_field,'sssss')/10)*10)/86400

where the combination of /10, ceil and *10 round things up to the nearest higher 10 seconds, and /86400 gives part of day.

Jaap. Received on Wed Jan 29 2003 - 15:57:41 CST