Archives of the TeradataForum

Message Posted: Fri, 16 Sep 2005 @ 18:04:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

After searching high-and-low for a quick way to round time to the nearest quarter hour, I have come up with this method. I am posting it here just in case someone else needs to do the same.

This sql converts the time to a fraction (same way Excel does), then divides fraction into 96 (number of quarter hours per day), rounds the number and converts back to standard time.

So 22:08:23 becomes 22:15:00 etc

     select
     ((cast((((extract(hour from current_timestamp) / 24.000000 +
     extract(minute from current_timestamp) / 1440.000000) + ( extract(second
     from current_timestamp) / 86400) )
     * 96) as decimal(4,0)) / 96.0000000) ) as qtr_hr_dec,
     (qtr_hr_dec * 24 (int) ) * 10000 + (qtr_hr_dec * 24) mod 1 * 6000
     (decimal(6,0)) (int) (format '99:99:99')

Jeff Ohlman
Sage Information Systems
www.sageinformationsystems.com/

	<Prev	Next>		<<First	<Prev	Next>	Last>>