DateTime

Question

DateTime

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
April 26, 2010 at 10:23 pm

#221938

Comments posted to this topic are about the item DateTime
MH-09-AM-8694

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Kiran gazzy SSC Eights! Points: 943 More actions · Answer 1

Good question. But i thought number of options provided are very less. This makes it easy for the people to guess the answer.

Kiran Gajendragadkar

Tech Mahindra bangalore

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Thanks Kiran, for your valuable comments.

--Mahesh[/font]

MH-09-AM-8694

WayneS SSC Guru Points: 95460 More actions · Answer 3

I was a little bit disappointed that the answer went into how a datatime data type is stored in two ints, and did not discuss why "1" = "1900-01-02".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

JF1081 SSC Eights! Points: 982 More actions · Answer 4

WayneS (4/27/2010)
I was a little bit disappointed that the answer went into how a datatime data type is stored in two ints, and did not discuss why "1" = "1900-01-02".

Same here. So I tried: Select Cast(2 As DateTime)

Select Cast(0 As DateTime)

Select Cast(365 As DateTime)

Select Cast(364 As DateTime) and a few other numbers. It looks like whatever number I put in there is how many days past 1900-01-01 00:00:00.000.

johnf_amic Mr or Mrs. 500 Points: 532 More actions · Answer 5

SQLServer stores the DateTime in an 8-byte field. The first four bytes are the number of days since 1/1/1900. The other 4 bytes represent some fraction of a second past midnight. This is an implementation choice, it is not a defined interface.

Try this:

Select Cast(getdate() As Binary(8))

You'll get something like:

0x00009D6500A63B12

The "date" part here is x0009D65, or 40,293. When you add '1' to a date, you are adding one to the date part of the 8-byte value:

Select Cast(getdate() + 1 As Binary(8))

0x00009D6600A6C675

I think the reason behind the answer is that SQLServer tries its best to guess at what you mean.

If you have an integer, it treats it as the number of days past 1/1/1900. This is also consitent with something like:

SELECT Getdate() + 1

If negative, it's the number of days before 1/1/1900:

Select Cast(-10 As DateTime) - Dec 22, 1899.

If you pass in a string to CAST, it tries to make sense of the string:

Select Cast('1/23/1945' As DateTime) - what you would expect (at least in the US).

Select Cast('10:32' As DateTime) - 10:32 am on 1/1/1900

Select Cast('4/27/10' As DateTime) - 4/27/2010!

Select Cast('4 april 2010' As DateTime) - perfectly valid.

try these:

Select Cast(1-1-1900 As DateTime)

Select Cast(1/1/1900 As DateTime)

Select Cast(01/01/01 As DateTime)

Select Cast(01/01/00 As DateTime)

Hint: They are not date strings. They are arithmetic operations!

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

OCTom SSChampion Points: 11785 More actions · Answer 6

OCTom

SSChampion

Points: 11785

April 27, 2010 at 10:04 am

#1158350

Good question. Thanks. 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Straightforward question. Thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SpaghettiCode Say Hey Kid Points: 709 More actions · Answer 8

Thanks for the question. One thing that confuses me is why

Select Cast(0 As DateTime) returns 1900-01-01 00:00:00.000

and

select datediff(dd, 0, getdate()) returns 40295 and is calculating the difference from today back to some date in 1753.

Seems slighly inconsistent, but probably is just my lack of understanding.

Can anyone clarify?

Thanks!

triloba Mr or Mrs. 500 Points: 582 More actions · Answer 9

select datediff(dd, 0, getdate()) returns 40295 and is calculating the difference from today back to some date in 1753.

This returns 40348 today (2010-06-21). 40348 / 365 = 110.5....

2010 - 110 = 1900

Not sure why/how you say it calculates to some date in 1753.

I'm using SQL 2005.

Tom

SpaghettiCode Say Hey Kid Points: 709 More actions · Answer 10

Yeah, you are certainly correct. Not sure what I was thinking when I posted this. Maybe got confused with min date in sql server 2005 which I believe is Jan. 1st 1753 and some bad math in my head. No inconsistency in the functions, only in my head....