TSQL Rounding or Truncating DateTime 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;

This code was tested and confirmed functional on SQL Server 2005, SQL Server 2008, SQL Server 2008R2 and SQL Server 2012.

And the output generated looks like this.

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

-Steve Stedman
Bellingham WA.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!