TSQL Rounding or Truncating DateTime to Nearest Day, Hour, Minute or Second

Download PDF

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.

Tagged with: , ,
6 comments on “TSQL Rounding or Truncating DateTime to Nearest Day, Hour, Minute or Second
  1. Lee Everest says:

    Thanks. I’m creating a minute dim and the formula working well for me

    -Lee-

  2. Amit says:

    Thanks for this lovely work. Helped me get the rounded date. Super :D

  3. Rimma says:

    Thanks Steve, very useful post

  4. Brian says:

    Nice, but I was hoping to learn how to TRUNCATE or chop off the milliseconds portion of datetime while still keeping the type as datetime. For example:
    7/25/2016 9:21:39.245 becomes
    7/25/2016 9:20:39

  5. AJ says:

    Round to nearest Minute.

    CONVERT(smalldatetime, @dtime)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.