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:

  1. DATETIME2, especially DATETIME2(0). If you’re using UTC everywhere, this is a no-brainer best data type. You get to choose whether you want your DATETIME2 to be smaller than DATETIME or more precise than DATETIME.
  2. 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:00 they enter something that rounds to 2020-01-01 00:00:00.003.
  3. 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 DATETIME2 and use it everywhere. Note that you also need to use the AT TIME ZONE syntax 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.
  4. TIME. This is where the utility curve really starts to fall off. I rarely use the TIME data type. However, I do appreciate that it’s just 3 bytes and can be useful for process scheduling.
  5. 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 (DATE and TIME if you use one but not both elements, and DATETIMEOFFSET if you don’t want to mess up time zones).
  6. 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”

  1. Technically, DATETIMEOFFSET doesn’t track the time zone; it tracks the offset from UTC. That’s not the same thing – just ask Jon Skeet! 🙂

    Reply

    1. 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.

      Reply

Leave a comment