Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  
Next> Last>>  

Subj:   Rounding time to the nearest quarter hour
From:   Jeff Ohlman

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

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

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020