Has date type changed in SQL Server 2008?

Question

I am confused reading statements in "Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?"

Was there date type in SQL Server before 2008 version? I cannot find.
In SQL Server 2008 zero date is 0001-01-01. Were there any date type before (in previous SQL Server versions) how is it backward compatible?

Mitch Wheat · Accepted Answer · 2010-10-20T03:59:01.117

3

No. Prior to SQL Server 2008, there was only the datetime and smalldatetime types.

Data type...............Range..................................................................Accuracy
datetime..................January 1, 1753, through December 31, 9999.....3.33 milliseconds

smalldatetime..........January 1, 1900, through June 6, 2079............... 1 minute

Please see: Date and Time Data in SQL Server 2008, specifically the bit that says "Date/Time Data Types Introduced in SQL Server 2008"

edited Oct 20 '10 at 03:59

answered Oct 20 '10 at 02:43

Mitch Wheat

295,962
43
465
541

I asked about date type but not about other types – Gennady Vanin Геннадий Ванин Oct 20 '10 at 02:46
2

@vgv8: And I just told you there wasn't one!! Rudeness will not get you help. – Mitch Wheat Oct 20 '10 at 02:47
Being exact or specific is not synonym of rudeness. You answered to something I had not ever asked. I see it in opposite way. Where is any date type in your answer? – Gennady Vanin Геннадий Ванин Oct 20 '10 at 03:20
@vgv8: I have answered your question. – Mitch Wheat Oct 20 '10 at 03:59
Thanks, I just needed a 3d (or, it happened, 2nd) opinion – Gennady Vanin Геннадий Ванин Oct 20 '10 at 04:11
@gbn, the notion of rudeness is subjective. IMO, rudeness is answering without bothering to understand (& even to read/listen) question. Some I simply deleted having realized that the answerers are going to mutually answer their own answers. I do not insist in discussing persons instead of things. Plz note the difference and stop giving your personal evaluations like in answer to my latest (deleted by me) question:"Don't quite understand what the question is asking. It sounds like you're thinking too much about a non-existent issue". That was not rude, that was an attempt to humiliate – Gennady Vanin Геннадий Ванин Oct 23 '10 at 02:44
@vgv8: .If the answer wasn't what you wanted, then perhaps the question needed to be rephrased? Anyway, you have an answer to your question, so all's good. – Mitch Wheat Oct 23 '10 at 03:41

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

In SQL Server datetime datatype the minimum date that can be stored is 1 Jan 1753.

However the datetimes are stored as numeric offsets to a base date of 1900-01-01 00:00:00.000

SELECT CAST(-0.25 AS DATETIME) /*1899-12-31 18:00:00.000*/
SELECT CAST(0 AS DATETIME) /*1900-01-01 00:00:00.000*/
SELECT CAST(0.25 AS DATETIME) /*1900-01-01 06:00:00.000*/

1899-12-30 has no significance in SQL Server.

SELECT CAST(CAST('1899-12-30' AS DATETIME) AS FLOAT) /*Returns -2*/

Has date type changed in SQL Server 2008?

2 Answers2