Date Rounding Tactics and the Tiny Devil of SMALLDATETIME

With every new year I think a little bit about time and dates. This posts looks a little more at that in TSQL.

Rounding Dates: Which way is best?

Sometimes in TSQL you need to round a datetime value to the precision of either a day, hour, minute, or second.

I realized recently that I have a few ways I know how to do this, but I wasn’t sure which was the most efficient.

I did a little searching and didn’t find anything super conclusive. I had a little chat with Jeremiah Peschka (blog | twitter) and he told me which way he thought was fastest and why.

And so I decided to run some tests. Jeremiah has a way of being right about these things, but I had to see for myself.

I’ll go ahead and tell you: He was totally right, and I’ll show you why. But I learned a couple things along the way.

Reference: Types and Storage Sizes

To get started, let’s review some of our friends, the SQL Server datatypes. Hi friends!

Rounding to the Day

The most frequent case in which I need to round dates is to the day level. So instead of ‘1/4/2011 6:15:03.393921’, I want just ‘1/4/2011’.

SQL 2008’s date type made this a lot easier for everyone– now we can just cast a datetime or datetime2 value as a date, and we’ve got what we need. PLUS, our new value is nice and small, weighing in at 3 bytes.

I think most everyone agrees, we like this!

SELECT CAST('1/1/2010 23:59:59.000' AS DATE) AS [I'm a date!]

Rounding to Hours, Minutes, or Seconds: Beware the tiny devil of SMALLDATETIME

This is still a bit more complicated. When you start thinking about these and different datatypes, you need to make sure you understand what you mean by rounding.

In SQL Server, our datatypes actually have some different opinions about what rounding means. Check this out:

CAST('1/1/2010 23:59:59.000' AS DATETIME) AS [I'm a DATETIME!],
CAST('1/1/2010 23:59:59.000' AS DATETIME2(0))  AS [I'm a DATETIME2(0)!'],
CAST('1/1/2010 23:59:59.000' AS SMALLDATETIME) AS [I'm a SMALLDATETIME, and I'm very confused.],
CAST('1/1/2010 23:59:59.000' AS DATE) AS [I'm a DATE!]

This returns:

The SMALLDATETIME value rounds this up to January 2nd, instead of January 1. The Date datatype does not.

In considering whether or not to use SMALLDATETIME, you need to learn and establish whether or not to round up for minutes and date values. With a different example, if something occurred at 12:30:31 AM, would that be represented as having  happened in the 12:30 minute, or at 12:31?

Most of us actually want to round down. We want the largest minute number which is less than or equal to the datetime value. This is similar to what FLOOR does for integers. You could also call this truncating the portion of the datetime value you don’t want.  This is not, however, what SMALLDATETIME gives you, so use it with care.

So this is what I’m saying:

Like, seriously, SMALLDATETIME: you are SO messed up.

Comparing Methods of Rounding Dates

So given that warning, let’s actually round some date values, and let’s compare the efficiency of each method.

To start out with, let’s create a table and toss in a bunch of date values. We’ll run queries against these dates and measure SQL Server’s abilities to work with it.

To make up a bunch of datetime data, I’m using my trusty recursive CTE from my prior post.

--Populate a table with some data
CREATE TABLE dbo.Donuts ( DonutTime DATETIME2(7) )

@startDate DATETIME2(7)= '2010-12-01 00:00:00' ,
@endDate DATETIME2(7)= '2010-12-11 01:30:00' ;

@startDate AS [Makin' the Donuts]
DATEADD(ms, 1225, [Makin' the Donuts])
[Makin' the Donuts] < @endDate )
INSERT  dbo.Donuts
[Makin' the Donuts]

--We now have 709716 rows of DonutTime

Now let’s look at different methods to manipulate datevalues. For our examples I’ll be rounding to the minute.

Contestant 1 –
DATEPART: isolate each part of the date, then concatenate

As we learn TSQL, this is the first method that occurs to us. We know DATEPART will return part of a date (great name!), so we can chop apart the bits. However, to get them back together properly we have to turn each part into a string to clue them back together. And then if we want to treat it like a date (which we pretty much always do), we have to cast it back.

Just look at this baby. It’s pretty ugly.

CAST(CAST(DATEPART(YY, DonutTime) AS CHAR(4)) + '-' + CAST(DATEPART(MM, DonutTime) AS NVARCHAR(2)) + '-'
+ CAST(DATEPART(DD, DonutTime) AS NVARCHAR(2)) + '  ' + CAST(DATEPART(hh, DonutTime) AS NVARCHAR(2)) + ':'
+ CAST(DATEPART(mi, DonutTime) AS NVARCHAR(2)) + ':00.000' AS DATETIME2(0)) AS [Wow, that was a lot of typing.]

Running this (after cleaning out buffers), I got these results:

Contestant 2 –
Subtracting what you don’t want

There’s a couple of variations on contestant #2. I’ll take the one I like best, which is casting to a smaller byte size by using DATETIME2(0), which is 6 bytes rather than 8 and effectively truncates to the second. Then I’ll subtract the seconds.

DATEADD(ss, -DATEPART(ss, DonutTime), CAST (DonutTime AS DATETIME2(0)))

Running this one (yes, I cleaned out the buffers), I got these results:

Well now, that’s much lower CPU time there.

NB: I did test, and in all my trials it was lower CPU time to cast into DATETIME2 rather than using a nested DATEADD function to subtract milliseconds.

Contestant 3-
Convert to a shorter character string, then back to date

This contestant is near and dear to my heart. I like it because it’s easy for me to remember. You take a short trip into CHAR() with the 121 date format and set the length to chop off the parts of the date you don’t want. Then you cast or convert back to a DATETIME2(0).

I think I like this one because it feels just a little bit violent. But not in a bad way. It’s like roller derby.

CAST(CONVERT(CHAR(16), DonutTime, 121) AS DATETIME2(0))

Oh, sad. This one didn’t do very well. It’s definitely better than Contestant #1, at least.

Contestant 4-
Use DATEADD to calculate the minutes since a given date, then add them back

Here’s the method Jeremiah suggested to me. The way he described it was “Just figure out the number of minutes since the beginning of time, and use that.”

Being a philosophy major, I of course asked “So, when was the beginning of time?”

Being a developer, he answered, “Just call it zero.”

DATEADD(mi, DATEDIFF(mi, 0, CAST(DonutTime AS DATETIME2(0))), 0)

Here are the results (clean buffers, as usual):

Ooo, check out the CPU time on that one.

Note: I ran a few trials and this is faster on the CPU when you cast as DATETIME2(0) before doing your maths. I did that to make all things equal with the other contestants, who had the same benefit.

Who Won, and Why

Here’s a recap of how everyone performed:

Why did contestants 2 and 4 do so well?

Jeremiah pointed out that datetime values are stored internally as two four byte integers. (BOL reference: see “Remarks”) Performing mathematic functions on an integer value is a nice fast activity on the CPU.

Performing conversions back and forth to character based datatypes, however, is not so natural, nor so fast.

What’s the internal storage format of DateTime2?  Well, I’m not sure about that one. BOL isn’t so up-front about these things anymore. If you happen to know, please tell me in the comments. I can tell, however, that it’s something that enjoys mathematics.

9 Comments. Leave new

  • Peter Larsson (Twitter @SwePeso) has a good writeup on the internals of the new DateTime data types (and the old ones):

  • George Mastros
    January 6, 2011 9:20 am

    Your smalldatetime value got weird because small date time only has precision to the nearest minute. Since you were giving it seconds, it rounded for you.

    Try converting the following 2 strings to small datetime, and you’ll see what I mean.
    Select Convert(SmallDateTime, ‘1/1/2010 23:59:29.99’)
    Select Convert(SmallDateTime, ‘1/1/2010 23:59:30.00’)

    By the way, DateTime has similar weirdness, but you need to use milliseconds to see it.

    Select Convert(DateTime, ‘1/1/2010 23:59:59.999’)

    • Thanks for the comment, George!

      I agree about SMALLDATETIME– I am trying to say that I don’t think the way it rounds is intuitive, and that many people would get results different than what they expect when using it.

      I like your point about Datetime. I am going to go back and update the post with more about datetime and the way that “datetime values are rounded to increments of .000, .003, or .007 seconds”.

      So for Select Convert(DateTime, ‘1/1/2010 23:59:59.995’), we get: 2010-01-01 23:59:59.997 (Rounding up 2 ms!)
      And for Select Convert(DateTime, ‘1/1/2010 23:59:59.998’), we also get: 2010-01-01 23:59:59.997 (Rounding down 1!)
      And as you mention, it rounds up 1 ms for 999 milliseconds (Which has all sorts of implications when defining a partition function).

      Thinking about your comment, maybe a prescriptive summary of what I’m saying is that if using SMALLDATETIME for the smaller storage size, users should decide if they want to truncate second values from dates. If so, they should consider using something along these lines:

      SELECT CONVERT(SMALLDATETIME, DATEADD(mi, DATEDIFF(mi, 0, ‘1/1/2010 23:59:50’),0))

  • You could make the same point about any rounding vs truncation cast: eg double -> float vs double -> int. One will round, one will floor. Whilst I’m sure the behavior will catch some people out sometimes, it seems entirely logical and hardly ‘messed up’ at all

    • Hi Piers,

      That’s an interesting point. Similarly, using the new datetime2 with second level precision still rounds up at anything .5 ms and above:

      SELECT CAST(‘1/1/2010 23:59:59.5’ AS datetime2(0))

      Still rounds and returns ‘2010-01-02 00:00:00’

      So why am I being so picky about SMALLDATETIME?

      I think SMALLDATETIME is particularly notable because every example I can think of where the rounding came into play would result in data that I believe end users would find incorrect.

      This is one of those “how people interpret dates” situations, and while I think people can accept, in many cases (certainly not all), rounding high millisecond values up, rounding up to the next minute after 30 seconds just isn’t how people think of time.

  • Reply
  • Kendra: I’ve read much of your writing and I like it! This may deserve it’s own thread but here goes…
    Our SQL Server 2008 database receives data with a pre-existing TimeStamp from a system each minute. When the consecutive TimeStamps are on the Cusp of the minute, we may get times like 2014-06-16 11:31:00.000 and 2014-06-16 11:31:59.000 respectively. In this situation, rounding to the minute produces a ‘duplicate’ record when in reality, they are not. We also can get 2 consecutive times such as 2014-06-16 11:25:58.000 and 2014-06-16 11:27:01.000. In this situation, rounding to the minute will appear like we’ve missed a minute whereas in reality, we have not. SO…In some cases we need to ROUND and in some cases we need to TRUNCATE. Also, missing records are a real possibility and we need to show that the records are truly ‘missing’ in these cases. To show missing records, I’ve created a Table valued function (we’ll call that the ‘minutes’ table) that returns 1440 rows for 1 day with 1 record for each minute. Then I perform an left join on the ‘minutes’ table which allows me to see the missing records. My challenge lies in ‘massaging’ the historical records and determining whether to ROUND or TRUNCATE. It appears to me that generating a CTE with ROWNUMBER() and joining the CTE on its self to get the previous and current records on the same row, along with a series of nested case statements to evaluate the conditions is the way to go…Have you faced this situation before? And can you help in any way.

  • Contestant 2 returns wrong results. The reason is that you use different types for the source operands of the subtraction.

    DATEADD(ss, -DATEPART(ss, DonutTime), CAST (DonutTime AS DATETIME2(0)))

    The seconds are extracted directly from datetime2 while the base is datetime2(0). Because of rounding issues this will not always result in the expected truncation. The above expression returns :01 seconds for some input values.

    The right expression is

    DATEADD(ss, -DATEPART(ss, CAST (DonutTime AS DATETIME2(0))), CAST (DonutTime AS DATETIME2(0)))

    Now the seconds part is always zero.

    However, note that the cast to DATETIME2(0) causes rounding similarly to SMALLDATETIME. The only difference is that it will round up only to full seconds. But this might change the date as well.
    This is also the reason why contestant 2 failed.


Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: