SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateTime


DateTime

Author
Message
Mahesh Bote
Mahesh Bote
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5555 Visits: 1303
Comments posted to this topic are about the item DateTime

MH-09-AM-8694
Kiran gazzy
Kiran gazzy
SSC Eights!
SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)SSC Eights! (877 reputation)

Group: General Forum Members
Points: 877 Visits: 130
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
Mahesh Bote
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5555 Visits: 1303
Thanks Kiran, for your valuable comments.

--Mahesh


MH-09-AM-8694
WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49778 Visits: 10852
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

JF1081
JF1081
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 322
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
johnf_amic
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 277
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
OCTom
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6017 Visits: 4152
Good question. Thanks. :-D
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145825 Visits: 18652
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
Learn Extended Events

SpaghettiCode
SpaghettiCode
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1163
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
triloba
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 202
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search