How does date difference work in SQL Server 2012

Question

I am running following query in SSMS. The result of this query does not make any sense.

Question : What logic is exactly followed by SQL Server in determining this difference as it relates to this query?

  SELECT
  CAST('1/1/1900 11:00:00 PM' AS DATETIME) AS Date1,
  CAST('1/1/1900 7:00:00 AM' AS DATETIME) AS Date2,
  CAST('1/1/1900 7:00:00 AM' AS DATETIME) - CAST('1/1/1900 11:00:00 PM' AS DATETIME)
  AS Date2MinusDate1

Result of this query

Date Difference in SQL Server 2012

Giorgi Nakeuri · Accepted Answer · 2015-04-16T21:57:45.740

The thing is that internally datetime data is stored as 8 byte consisting of 2 integers: one that represents days after 19000101 and other representing ticks after midnight. So actually you are substracting 2 integers here:

Day of DT1 - Day of DT2
Ticks of DT1 - Ticks of DT2

Days are 0s since 19000101 is saved internally as 0. So first substraction gives you 0, i.e. 19000101.

The difference of ticks will give you 16 hours, but pay attension, it will be ticks(16 hours) from midnight, so actually you should substract 16 hours from midnight not 7am. Thus 00:00 - 16 hours is equal to 08:00. Of course here you shoulb decrement the result of substruction of day, i.e. 0-1=18991231.

So you end up with 18991231 08:00 and this is correct output.

But you should be very careful when using operators for date manipulation because there are nuances. Use native functions for such manipulation, like DATEADD. You can read this article, especially from page 2 http://www.devx.com/dbzone/Article/34594/0/page/2

No it is not. Read this from page 2 http://www.devx.com/dbzone/Article/34594/0/page/2 — Giorgi Nakeuri, Apr 16 '15 at 21:50
I think you should write this fact as part of your answer that substraction is not reliable for datetime values. — Sunil, Apr 16 '15 at 21:52

score 1 · Answer 2 · edited May 23 '17 at 12:14

1

The 12/31/1899 is SQLs equivalent to a zero date.

This is a holdover from bad date compatible software.

See this answer: Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?

I would recommend DATEDIFF for finding differences.

SELECT
  CAST('1/1/1900 11:00:00 PM' AS DATETIME) AS Date1,
  CAST('1/1/1900 7:00:00 AM' AS DATETIME) AS Date2,
  DATEDIFF ( hh , CAST('1/1/1900 7:00:00 AM' AS DATETIME) , CAST('1/1/1900 11:00:00 PM' AS DATETIME)) AS Date2MinusDate1

edited May 23 '17 at 12:14

Community

1
1

answered Apr 16 '15 at 18:15

Cyberdrew

1,832
1
19
39

But it looks like datetime subtraction is reliable, because if we add Date1 + Date2MinusDate1, then we get Date2. So, it seems that there is consistency in datetime arithmetic in SQL Server 2012. – Sunil Apr 16 '15 at 19:24
1

It may have something to do with date formatting. if your run: SELECT CAST('1900-01-01 23:00:00.000' AS DATETIME) AS Date1, CAST('1900-01-01 07:00:00.000' AS DATETIME) AS Date2, CAST('1900-01-01 23:00:00.000' AS DATETIME) - CAST('1900-01-01 07:00:00.000' AS DATETIME) AS Date2MinusDate1 you get 4:00PM which would accurately translate to the 16 hour difference. – Cyberdrew Apr 16 '15 at 19:41

How does date difference work in SQL Server 2012

2 Answers2