Over on Curated SQL today, I linked to Deborah Melkin’s review of the different date and time data types available in SQL Server.
As I was reading that, I decided that it was time to create an Official Ranking. So here it is for the date and time data types:
DATETIME2(0). If you’re using UTC everywhere, this is a no-brainer best data type. You get to choose whether you want your
DATETIME2to be smaller than
DATETIMEor more precise than
DATE. A large percentage of the time, I just need the date and don’t care about the time. This three-byte beauty gives me that, but with zero risk that I miss a record because instead of
2020-01-01 00:00:00they enter something that rounds to
DATETIMEOFFSET. It’s a hefty data type, the largest in the bunch. But if you aren’t dealing with UTC everywhere, it does something no other date or time data type can: it keeps track of your time zone. Therefore, if you for some reason hate storing times in UTC, flip this with
DATETIME2and use it everywhere. Note that you also need to use the
AT TIME ZONEsyntax to specify what the time zone is or have your user correctly specify the time zone by passing in a pre-created
DATETIMEOFFSET. It’s like getting a puppy: you wanted it, now you deal with the extra work taking care of it.
TIME. This is where the utility curve really starts to fall off. I rarely use the
TIMEdata type. However, I do appreciate that it’s just 3 bytes and can be useful for process scheduling.
DATETIME. It’s really not that great of a data type. If you care about size, three of the four have it beat. If you care about precision, three of the four (assuming you bump up the precision on
DATETIME2) have it beat. If you care about not messing things up, three of the four have it beat (
TIMEif you use one but not both elements, and
DATETIMEOFFSETif you don’t want to mess up time zones).
SMALLDATETIME. If you’re really that desperate about two bytes, just use
DATETIME2. This was a lot more useful prior to SQL Server 2008, but we can say the same thing about Kurt Warner. That doesn’t make me want to plug him into my fantasy team today.
2 thoughts on “Official Rankings: Date and Time Data Types in SQL Server”
Technically, DATETIMEOFFSET doesn’t track the time zone; it tracks the offset from UTC. That’s not the same thing – just ask Jon Skeet! 🙂
That’s true, as Indiana EST (the three counties which never go on Daylight Savings Time) will share -0500 with the rest of EST until it goes on Daylight Savings Time, and there are shenanigans around where something was stored in EST as -0500 and then the time zone becomes EDT at -0400. I suppose to be technically accurate we’d also collect the time zone itself as a separate column to perform the correct AT TIME ZONE. But the general point is still there: it’s the only datatype which even lets us talk about this, as all the rest pretend that everybody lives and works and acts in one time zone. Which would be okay if it was EST, as that is, after all, the Standard Time.