Why Does the DATEADD(d, DATEDIFF(d, 0…) Method Strip Off the Time From DATETIME?
To be accurate, in SQL Server 2000 or below, the DATETIME datatype always contains both: A date and a time portion. You cannot have one without the other. So it is a bit misleading to say “strip off the time”. You can, however, set this time portion to midnight to avoid incorrect results in your queries, as I’ve shown above. One of the many possible methods to set the time to midnight:
SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
——————————————————
2005-03-23 00:00:00.000
(1 row(s) affected)
But how and why does it work? Such analysis is mostly done best from the inside out. So, the first statement to look at is:
SELECT DATEDIFF(d,0,GETDATE())
———–
38432
(1 row(s) affected)
Voila, and that’s already just the whole trick!
The DATEDIFF function with the day datepart parameter set returns the number of days between two dates in day boundaries. “Day boundaries” is possibly best explained with an example:
SELECT DATEDIFF(d,’20050228 23:59:59.997′, ‘20050301 00:00:00.000’)
———–
1
(1 row(s) affected)
Nobody would seriously say that a whole day has elapsed between both dates. But when you, like the DATEDIFF function, only consider:
SELECT DATEDIFF(d,’20050228′, ‘20050301’
———–
1
(1 row(s) affected)
you have no other chance but to state 1 day has elapsed. So, getting back again to our example, DATEDIFF returns the days between the base date and today as an integer value. How good of that function! So at this moment we have our desired result; today’s date without a time portion. We now only need to get it back into a DATETIME. That can be done via DATEADD.
SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0) , DATEADD(d,38432,0)
—————————————————— ——————————————————
2005-03-23 00:00:00.000 2005-03-23 00:00:00.000
(1 row(s) affected)
While writing this article and thinking about it, I’m tempted to say that a simple
SELECT CAST(DATEDIFF(d,0,GETDATE()) AS DATETIME)
——————————————————
2005-03-23 00:00:00.000
(1 row(s) affected)
should also do the trick just fine.
You see, there is no magic date or integer arithmetic involved. Frankly, it’s frustratingly simple. And it also works this way with all the allowed date parameters for DATEDIFF and DATEADD.
On a side note: You can also take advantage of the internal storage format of a DATETIME to set the time to midnight, like this:
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME) , CAST(CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) AS INT) AS DATETIME)
—————————————————— ——————————————————
2005-03-23 00:00:00.000 2005-03-23 00:00:00.000
(1 row(s) affected)
Why Does My Query …WHERE [datetime column] = ” return rows?
Well, this one is actually either a funny one or an annoying one. This depends in which mood you are in the moment you discover this behavior. Suppose you have the following table:
CREATE TABLE #InconsequentImplemententationByMS ( dt DATETIME DEFAULT ‘19000101’ ) INSERT INTO #InconsequentImplemententationByMS DEFAULT VALUES
For some reasons you’ve decided to have a DEFAULT of 19000101 on the DATETIME column. Which is, by the way, perfectly legal, IMHO. But now Mr. KnowsItAll, the Power User from the sales department, smartly decides to run
SELECT * FROM #InconsequentImplemententationByMS WHERE dt=”
in his VBA app to open an empty Adodb.Recordset. Or, for whatever reason there else might be. We intentionally ignore here the fact that there are better ways for opening an empty recordset. We also do not try to understand the reason why Mr. KnowsItAll tries to compare a DATETIME value with an empty string.
No, here we rather focus on the interesting and yet surprising return of that query:
dt
——————————————————
1900-01-01 00:00:00.000
(1 row(s) affected)
What the heck is going on here? How can an empty string be evaluated by SQL Server to a valid date?
Consider this:
SELECT CAST(” AS DATETIME)
——————————————————
1900-01-01 00:00:00.000
(1 row(s) affected)
And to be more precise:
SELECT CAST(CAST(” AS INT) AS DATETIME)
——————————————————
1900-01-01 00:00:00.000
(1 row(s) affected)
When you resolve this again from the inside out, you’ll get:
SELECT CAST(” AS INT)
———–
0
(1 row(s) affected)
Apparently, SQL Server CASTs an empty string to 0. Coincidentally, this is also SQL Server’s base date. This is why the empty string in the example is evaluated as a valid date and returns a resultset.
Frankly, I am unsure if I should laugh or cry about this implementation. I can only guess what the developers at Microsoft had thought on this, but I work a lot with Excel, so I’m used to the fact that an empty cell in an Excel spreadsheet actually contains a 0 to make sure no calculation will break. Maybe both MS development teams swapped this idea during lunch break or something. Maybe Microsoft wanted to make it a bit easier for someone. Maybe some strange developer joke… Who knows? It is as it is and it is so “by design”. But I think SQL Server should rather throw an error than silently convert this.
And on top of this:
SELECT ISDATE(”)
———–
0
So, here the implicit conversion not work. ISDATE() returns FALSE. However,
SELECT ISDATE(CAST(” AS DATETIME))
———–
1
(1 row(s) affected)
again returns TRUE.
Last one on this
SELECT ISDATE(CAST(” AS INT))
———–
0
(1 row(s) affected)
It again returns FALSE. There are quite a few examples of ISDATE()’s return value mentioned in BOL. An example with an empty string is not among them.
I’ll leave it up to the reader to decide if this is good implementation of a datatype or not. Once one knows about it, one can pretty well live with it. However, figuring this out for the first time, maybe the hard way, isn’t that funny at all.