TSQL Rounding or Truncating Date Time to Nearest Day, Hour, Minute or Second

One thing that I end up having to look search on regularly is rounding of dates and times in Transact SQL, having looked this up too many times I finally realized that it is time for me to do my own blog post for it.

First off, whats the difference between rounding and truncating in these examples. Rounding rounds to the closest second, so 10:00:31 is rounded up to 10:01:00, and 10:00:29 is rounded down to 10:00:00. With truncation, it simple changes the truncated area to 0’s. so 10:00:31 gets truncated down to 10:00:00, and so does 10:00:59. Sometimes you may want rounding, and sometimes you may want truncation (floor) for your specific needs.

The following example shows how using datetime in SQL Server to round to the second, round to the minute, round to the hour and round to the day. It also includes the TSQL to truncate to the hour, minute and second.

The query:

declare @dtVariable as datetime;
set @dtVariable = getdate();

-- if you are testing this before noon, uncomment the
--     following line to simulate an after noon time.
--set @dtVariable = dateadd(hour, 12, @dtVariable)

-- Rounding to the second
select @dtVariable as Original,
       DATEADD(ms, 500 - DATEPART(ms, @dtVariable + '00:00:00.500'),
	           @dtVariable) as RoundedToSecond;

-- Truncated to the minute
select @dtVariable as Original,
       DATEADD(minute, DATEDIFF(minute, 0, @dtVariable), 0) as TruncatedToMinute;

-- Rounded to minute
select @dtVariable as Original,
       DATEADD(minute, DATEDIFF(minute, 0,
	         DATEADD(second, 30 - DATEPART(second, @dtVariable + '00:00:30.000'),
	         @dtVariable)), 0) as RoundedToMinute;

-- Truncated to the hour
select @dtVariable as Original,
       DATEADD(hour, DATEDIFF(hour, 0, @dtVariable), 0) as TruncatedToHour;

-- Rounded to hour
select @dtVariable as Original,
       DATEADD(hour, DATEDIFF(hour, 0,
	         DATEADD(minute, 30 - DATEPART(minute, @dtVariable + '00:30:00.000'),
	         @dtVariable)), 0)  as RoundedToHour;

-- Truncated to the day
select @dtVariable as Original,
       DATEADD(Day, DATEDIFF(Day, 0, @dtVariable), 0) as TruncatedToDay;

-- Rounded to day
select @dtVariable as Original,
       DATEADD(day, DATEDIFF(day, 0,
	         DATEADD(hour, 12 - DATEPART(hour, @dtVariable + '12:00:00.000'),
	         @dtVariable)), 0)  as RoundedToDay;

And the output generated looks like this.

I hope you find this as useful as I do on a regular basis.

TSQL Rounding or Truncating Date Time to Nearest Day, Hour, Minute or Second

Leave a Reply Cancel reply