Friends Don't Let Friends Use DATETIME

I know you love your Pontiac Aztek, but it’s time to move on from SQL Server 2005’s limited set of data types. Unless, of course, you’re stuck on SQL Server 2005. If that’s the case, then you should get working on your migration. For the rest of you, let’s talk about why you should stop creating new DATETIME columns.

Microsoft Says So

What a great reason to do it! The people who made your database don’t even want you using DATETIME for new applications. Don’t believe me? Check it out! [caption id=“attachment_984” align=“aligncenter” width=“913”]No, seriously, don’t use DATETIME. No, seriously, don’t use DATETIME.[/caption] Using DATETIME to store temporal data is, for the most part, ineffective. There are better options out there. Let’s look at the alternatives to help you understand the best data type for your needs.

DATE

Many applications only need the date portion of DATETIME. Using DATE makes life much easier - you don’t have to worry about always stripping off the time component, just in case some jerk accidentally saved that data, too. Plus, a DATE only takes up 3 bytes instead of 8. Over time, those 5 bytes add up. Save a million rows and that’s like… 5 megabytes. Or 3.5 3.5" floppy disks.

TIME

What if you just want to save the time? SQL Server has you covered there, too. TIME is also where things start to get interesting. You see, with TIME we can define the fractional seconds precision that we need. I know I sound crazy, but bear with me. If I’m only recording the time as humans need to view it (for a calendar), I don’t care if the time of our meeting is at 11:00:00.000 or 11:00:00.100 - that 100 milliseconds isn’t perceptible to me. With TIME we can specific TIME(0) to tell SQL Server not to store the fractional seconds. The upside is that TIME(0) only requires 3 bytes whereas TIME(7) (the max precision) requires 5 bytes. By default, though, SQL Server is a bit crazy and a TIME is actually created as TIME(7) - that’s the time with a 100 nanosecond precision. Because reasons.

DATETIME2 - The Re-Datetime-ening

This is basically an upgrade to DATETIME - it features the precision of TIME without the accuracy problems of the originalDATETIME. We can vary the precision of DATETIME2, which makes sense since it’s basically just DATE and TIME going on a date. To be honest, I can’t see a lot of reason to use DATETIME2 either because…

DATETIMEOFFSET

I heard, once, that there were people in the world who don’t live in my city. These people might live so far away from me that when the sun is directly overhead where I live, the sun is setting where they live. CRAZY! DATETIMEOFFSET has all the precision of TIME or DATETIME2 but adds one additional feature: time zone support.DATETIMEOFFSET stores data with the timezone offset from UTC. By doing so, we can very easily save data from multiple clients, in multiple locations, store it in a universal format, and then easily display data to end users in a format they’ll understand.

What’s It All Mean?

First off, if you’re building new applications, just don’t use DATETIME. Consider using one of the new alternatives like DATE,TIME, or DATETIMEOFFSET… Or DATETIME2 if you’re some kind of degenerate. Actually, that’s all it means. When you’re building an application, think about the type of data you need to store and the domain of that data. Pick the data type that best matches that data domain. And don’t be afraid of newer data types, I promise that they’re not going to eat you.