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,
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.
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
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.]