Dates and Times in SQL Server: the problem with DATETIME

Recently I wrote a post about date and time functions you should never use, which contained an opinion I’ve expressed previously that some people have disagreed with, namely that DATETIME is a terrible data type which you should not be using in new development. The motivation for this position is that a better data type exists.

As I mentioned previously, DATETIME uses eight bytes and has an accuracy of 3 milliseconds, as long as those milliseconds end in 0, 3 or 7. This provides for inaccurate values due to rounding errors, and possible data corruption if using DATETIMEFROMPARTS().

You can use DATETIME2(3) instead as a drop-in replacement for DATETIME, giving you higher accuracy (down to the millisecond) while using one less byte per column. Switching to it would be a no-brainer, right?

Not so fast! There is only one absolute in SQL Server, and that is to never use auto-shrink on a production database. Everything else — including my feelings on DATETIME — can be argued for and against. And argue, some folks have.

ANSI compliance

Two commenters in particular have weighed in on the post with reference to ANSI SQL. As I mentioned in previous posts, the American National Standards Institute (ANSI) sets standards which a relational database management system (RDBMS) can implement to be considered ANSI-compliant.

This is intended to avoid vendor lock-in. With a standards-compliant RDBMS you should (there’s that word again) be able to move to a different platform with little fuss, provided that both systems make use of ANSI-compatible data types and syntax, however:

Despite the existence of such standards, most SQL code is not completely portable among different database systems without adjustments. (Wikipedia)

Joe Celko’s comment addresses the “missing” parentheses on CURRENT_TIMESTAMP, going into some interesting detail why this is the case.

Jeff Moden’s comment does prompt a follow-up response though. Jeff states that by dismissing DATETIME, things become harder to do, and the newer data types are not ANSI-compliant because they don’t follow the “ANSI Standards, which say things like Period (duration)=EndDatetime-StartDateTime and EndDateTime=StartDateTime+Period.”

Yes, you’re right. ANSI compliance is extremely important if you write SQL code that has to work on different platforms with the same expected results. Choosing common data types across (for example) Oracle, SQL Server, PostgreSQL, and MySQL means going with the ANSI data types and the inherent limitations of those types, including rounding up milliseconds to end in a 0, 3, or 7 on SQL Server. Evaluating that choice is part of the risk assessment when embarking on a new project.

DATETIME is still a bad data type

If you’re working in SQL Server exclusively, on a version higher than SQL Server 2005 (or any version of Azure SQL Database), there’s no reason to use DATETIME. None. It’s a bad data type, and if you design new databases with this data type you should feel bad.

On the other hand, if you need to move data around between systems (like Jennifer or Jeff), you can use ANSI data types, keeping their limitations clearly in mind as well as their benefits.

Share your thoughts below, or find me on Twitter at @bornsql.

Photo by Kevin on Unsplash.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: