Skip to content
Home » Dates and Times in SQL Server: SMALLDATETIME

Dates and Times in SQL Server: SMALLDATETIME

  • by

Last week I spoke about a world wary data type for storing dates and times in a single column, with a granularity of three milliseconds, DATETIME.

But let’s say you don’t need that kind of accuracy and are happy with a granularity to the nearest minute. Maybe you’re storing time cards and don’t think it’s necessary to store seconds. As discussed in the Fundamentals series, you really want to choose the most appropriate data type for your data.

Enter SMALLDATETIME, which rounds up or down to the nearest minute. The seconds value for any SMALLDATETIME is 00. Values of 29.999 seconds or higher are automatically rounded up to the nearest minute, while values of 29.998 seconds or lower are rounded down.

The greatest advantage of this data type is that it only uses 4 bytes, as opposed to the 8 bytes used in DATETIME.

That’s pretty much where it ends on the list of advantages. Unfortunately, we can only store values from 1900-01-01 to 2079-06-06, so it’s fairly limited in scope.

Additionally, since SQL Server 2008 was released, the newer date and time data types make it easier to store dates and times, even with custom granularity.

If you’re creating a new database, don’t use SMALLDATETIME. There are better options, which we will cover next time.

Find me on Twitter at @bornsql.

[Edit: Thanks to Luis Gonzalez for pointing out a mistake in my original post.]

Tags:

2 thoughts on “Dates and Times in SQL Server: SMALLDATETIME”

  1. Sean Redmond

    Smalldatetime uses 4 bytes and can handle dates within an accuracy of 1 minute between 1900 and 2079. The only temporal datatype that is smaller than that is that of Date and it is 3 bytes. Yes, it supports a much, much wider range of dates, but how often do you use, let alone need, such esoteric dates?

    Datetime2(2) uses 6 bytes and gives me millisecond accuracy, which is great if it is necessary, but otherwise it is a waste of 2 bytes when it isn’t especially necessary.

    If I have a table with millions of entries (or more) and I want to record when a certain event happened, I will ask if millisecond accuracy (or finer is required), because it it isn’t, I’m wasting almost 2MB alone for every million records (2000000/(1024×1024)) or 244 pages.

    For me, the datetime2(2) datatype is a great replacement for the datetime datatype and date can often replace smalldatetime, but to say that are better options is being fanciful. There are newer options which handle a multi-timezone world with much more ease, but the smalldatetime datatype is still my default option for the storing of date and time unless the specifications call for greater specificity.

    1. randolph

      Thanks for your comment, Sean. I couldn’t reply until now as I was at a conference.

      First off, SMALLDATETIME is neither ANSI nor ISO 8601 compliant. The newer types (DATETIME2, DATE and TIME) do have some compatibility issues around leap seconds, but for the most part, they are compatible.

      Secondly, your DATETIME2(2) statement is incorrect. With two decimal places, you get just 10-millisecond accuracy. For millisecond accuracy (and a drop-in replacement of DATETIME with less space), you would need to use DATETIME2(3). I covered this two years ago in this post.

      Regarding your comment around “esoteric” dates, I have needed to record dates before 1900 (usually dates of birth) in many systems I’ve architected. Using the DATE type makes far more sense in this case than SMALLDATETIME, especially since I don’t need to record a timestamp.

      A small note: these “newer” data types have been around for a decade now. I think it’s time for you to consider using them. Modern systems expect forward-compatibility and standards-compliant design.

Comments are closed.