2

I have been using SQL for a little while now - having to use Oracle and SQL Server for my work - however, I have just come across something I have not seen before.

After seeing this and doing a bit of research it is said that, in SQL server, the number 0 can be used as the base date, which is:

1900-01-01

so for example: select DATEDIFF(yy, 0, '2017-12-31') would return 117, as 1900-01-01 substitutes the 0.

My first question is, why is this, considering this is not the minimum date value in SQL (which is the year 1753 I believe)?

My second question is that I came across another piece of SQL which uses the number -1 instead of 0.

After some testing, I can assume that it is referring to 1899-12-31. but i cannot be sure as I cannot find anything on this number being used as a date anywhere online. Am I correct?

Thank you for your time.

Kleo G
  • 247
  • 1
  • 4
  • 20
  • @Pac0 Actually it depends on the data type you are using. Please refer to https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql#DateandTimeDataTypes – Pred Dec 12 '17 at 12:27
  • @Pred okay this is assuming the datatype is a `date`. – Kleo G Dec 12 '17 at 12:29
  • A more interesting question is [why 1753?](https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server) – Zohar Peled Dec 12 '17 at 12:36
  • @ZoharPeled honestly, i wanted to ask this question as well but i thought it might be beyond the scope of this question. – Kleo G Dec 12 '17 at 12:39
  • Well, then, read the answers in the link. It's both educating and entertaining. – Zohar Peled Dec 12 '17 at 12:40

2 Answers2

3

First, there is little reason to use 0 as a date. The primary reason (originally) was to truncate the time component:

select dateadd(day, datediff(day, 0, <datetime>), 0)

Note that this would work with any date value, but 0 tended to be used.

This is now done using select cast(<datetime> as date).

But to answer your question. SQL Server does support negative numbers as dates. The reason for using "-1" is for compatibility with Excel. In Excel the "0" date is "1900-01-00" which is effectively "1899-12-31".

I'm not sure why Microsoft software has two different zero values for dates. (Well, I do . . . Microsoft historically acquired software rather than write it itself.) It is a little confusing, but that is probably why "-1" is being used.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Correct, in SQL Server, the date 0 represents 01 January 1900. 

0 = 19000101
1 = 19000102
2 = 19000103
...
43080 = 20171212

Unsurprisingly, therefore, numbers below zero work the same way:

-1 = 18991231
-2 = 18991230
...
-53690 = 17530101

As for why 0 is 1900, this likely dates back to prior to SQL Server, when it was called SyBase. Most likely, back then, 0 represented 19000101, and as SyBase become SQL Server, it was prudent to keep the the same process (as otherwise it could well break people's existing code).

Thom A
  • 88,727
  • 11
  • 45
  • 75