How are integers interpreted in contexts that expect a date?
I found a confusing construction in several stored procs in an MS SQL 2008 R2 database:
DATEADD(dd, 0, DATEDIFF(dd, 0, some_date))
As I understand it, these are the relevant function signatures:
DATEDIFF(datepart, startdate, enddate)
DATEADD(datepart, number, date)
That is, the proc supplies 0 as the startdate argument to DATEDIFF. The return value from DATEDIFF is an int, which in turn is supplied as the date argument to DATEADD. And the code does work -- or at least it runs without error.
So:
-
How is
0(or other integers) interpreted in contexts that expect a date? -
(bonus) What on earth did the author intend this to do? That DATEADD should be a no-op, however the integer is interpreted.
1 answer
SQL Server uses '1900-01-01' as a "zero-point" in DATEDIFF(dd, 0, some_date):
select DATEDIFF(dd, 0, '1900-01-01') --> 0
select DATEDIFF(dd, 0, GETDATE()) --> 44066 days since the "zero-day"
The whole expression is used to strip time from the DATETIME and still have it as a DATETIME (as opposed to DATE). An alternative would be to CAST it to DATE, but it will change the type:
select CAST(GETDATE() AS DATE) --> 2020-08-25 (no time)
What is more interesting is that the minimum DATETIME is not '1900-01-01', but 1753-01-01 which of course corresponds to a negative integer value:
select cast(-53690 as datetime)
Relevant resources:
SQL Server function to return minimum date from Stack Overflow.
Best approach to remove time part of datetime in SQL Server from Stack Overflow.
0 comment threads