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
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!
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.
Many applications only need the date portion of
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.
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.
This is basically an upgrade to
DATETIME – it features the precision of
TIME without the accuracy problems of the original
We can vary the precision of
DATETIME2, which makes sense since it’s basically just
TIME going on a date.
To be honest, I can’t see a lot of reason to use
DATETIME2 either because…
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
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.
First off, if you’re building new applications, just don’t use
DATETIME. Consider using one of the new alternatives like
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.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.