Archives of the TeradataForum
Message Posted: Fri, 16 Sep 2005 @ 18:04:10 GMT
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')
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|