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.
Guess I’ll continue to vehemently disagree with the notion of DATETIME being a bad data type. The ANSI compliant stuff I mentioned was mostly for Celko’s benefit. The things you can do easily do with the DATETIME datatype are incredible compared to the almost stupid restrictions that MS put on the newer temporal data types.
To be sure, one of the big reasons why I like the ANSI standards is because they allow direct date math, particularly what you cited about what I said about how easy is is to calculate durations quite accurately as well as it being quite easy. I won’t change my position on that.
I’ll also say that it wouldn’t take much to change my mind but Microsoft has been of no help even as 2022 is less than the horizon away from going public. Having recently to have to work with UNIX TimeStamps at the ms level has only added to the exasperation I feel. What on Earth was Microsoft thinking when they came out with the DATEDIFF_BIG() function without also producing a DATEADD_BIG() function? Further, it’s 2022… why is there no DURATION() Function? It’s not like people haven’t needed one since SQL Server and Sybase first hit the streets.
Oh, yeah… I know all the workarounds. My point is, it IS 2022 and these types of problems have existed in the world of relational databases for decades. We shouldn’t need to do such workarounds in this day and age.
Anyway, let me take this as another opportunity to say “Thank You” again, Randolph, for all of the great articles, presentations, and discoveries you’ve done and made in the past and continue to do. You’re definitely one of the “greats”.
Thanks Jeff. I hear where you’re coming from, and life has quite a different perspective now that I’m inside the belly of the beast.
Comments are closed.