Mark all as read

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.


  1. How is 0 (or other integers) interpreted in contexts that expect a date?

  2. (bonus) What on earth did the author intend this to do? That DATEADD should be a no-op, however the integer is interpreted.

Why should this post be closed?


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.

1 comment

Thanks. That makes sense, though the choice of method still seems crazy to me. I would think it would be simpler -- and definitely clearer -- to cast it to date and then cast back to DT. (or just...not cast it back and use the date as is. The difference wasn't important in-context) ajv‭ 3 months ago

Sign up to answer this question »