DateTime

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mahesh Bote » DateTime

11 posts, Page 1 of 2

1 2 »»

DateTime

Rate Topic

Display Mode

Topic Options

Author

Message

Mahesh Bote

Posted Monday, April 26, 2010 10:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

Comments posted to this topic are about the item DateTime

MH-09-AM-8694

Post #910812

Kiran gazzy

Posted Monday, April 26, 2010 10:41 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 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

Mahesh Bote

Posted Tuesday, April 27, 2010 2:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

Thanks Kiran, for your valuable comments.

--Mahesh

MH-09-AM-8694

Post #910875

WayneS

Posted Tuesday, April 27, 2010 7:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 6,386, Visits: 8,283

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

JF1081

Posted Tuesday, April 27, 2010 7:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 07, 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

johnf_amic

Posted Tuesday, April 27, 2010 9:08 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 7:15 AM
Points: 351, Visits: 243

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

OCTom

Posted Tuesday, April 27, 2010 10:04 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 2,027, Visits: 2,864

Good question. Thanks. BigGrin

Post #911270

SQLRNNR

Posted Tuesday, April 27, 2010 1:08 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:09 PM
Points: 18,848, Visits: 12,433

Straightforward question. Thanks.

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

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

SpaghettiCode

Posted Thursday, April 29, 2010 2:45 PM

SSC-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

dba-wannabe

Posted Monday, June 21, 2010 9:05 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:06 PM
Points: 10, Visits: 145

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions