SQL Server: Rounding DateTime to Intervals

There are times when you are wanting to produce bar charts that you wish to round times to the quarter hour and keep it as a time. The typical solution is to cast as a varchar() and use the string, unfortunately this works well only for some intervals. Doing this properly is not hard, but you need to be careful not to have numeric calculation artifacts creeping in. Below, I am trying to round everything to the closest quarter hour, other periods just require some change of numbers.

• 15 minutes –> 24 x 4 =96 Intervals
• 10 minutes –> 24 x 6 = 144 Intervals
• 1 minute –> 24 x 60 =1440 Intervals.

 

The Oops not perfect solution

The function below seems to create the needed result – until you test it and discover that the times are not always precise (arithmetic approximation  issues). This can cause problems with some usages.

 

ALTER FUNCTION QtrHr
(
    @When DateTime
)
RETURNS DateTime
AS
BEGIN
    DECLARE @est numeric(38,30)
    Set @est=Round(Cast(@When as numeric(38,30)) * 96.0,0)/96.0
    SET @When= Cast(@est as DateTime) 
    RETURN @When
END
GO
select dbo.QtrHr(GetDate()),GetDate()

 

A more reliable approach

I rewrote the function to eliminate reliance on mathematical functions being precise. I grab the day part and then the number of 15 minute intervals, combining them with the DateAdd function. Everything now works fine.

ALTER FUNCTION QtrHr
(
    @When DateTime
)
RETURNS DateTime
AS
BEGIN
    DECLARE @est numeric(38,30)
    Declare @day int
    Declare @min int
    Set @est=Round(Cast(@When as numeric(38,30)) * 96.0,0)
    SET @Day=@est/96.0
    Set @Min=(@est % 96)*15
    SET @When=DateAdd(mi,@min,Cast(@Day as DateTime))    
    RETURN @When
END