Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DateTime Expand / Collapse
Author
Message
Posted Monday, April 26, 2010 10:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
Comments posted to this topic are about the item DateTime

MH-09-AM-8694
Post #910812
Posted Monday, April 26, 2010 10:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 1:50 AM
Points: 829, 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
Post #910815
Posted Tuesday, April 27, 2010 2:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
Thanks Kiran, for your valuable comments.

--Mahesh


MH-09-AM-8694
Post #910875
Posted Tuesday, April 27, 2010 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 5,361, Visits: 8,924
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
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
Post #911080
Posted Tuesday, April 27, 2010 7:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, 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.
Post #911117
Posted Tuesday, April 27, 2010 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:48 AM
Points: 381, Visits: 270
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
Post #911219
Posted Tuesday, April 27, 2010 10:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 2,581, Visits: 3,883
Good question. Thanks.
Post #911270
Posted Tuesday, April 27, 2010 1:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
Straightforward question. Thanks.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #911382
Posted Thursday, April 29, 2010 2:45 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 19, 2013 11:30 AM
Points: 467, Visits: 1,163
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!
Post #913277
Posted Monday, June 21, 2010 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:23 PM
Points: 13, Visits: 158
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

Post #940390
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse