Magic Date?

Anyone know why 1899-12-30 is a special date?
If you put that date with a time in sql server EM and a vb call will return only the time portion...
More background and sample code
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30709In vb, the date is a floating-point where the integer portion is the date. 12/30/1899 is the base date. So if you enter a time only, the integer portion of the datetime will be 0 and the decimal portion will be the time.|||What's EM Built in? If you just enter a time through EM, it'll ne 1899-12-30...

And isn't 1900-01-01 the 0 date for sql server?

SELECT CONVERT(datetime,0,101)

???

And why, if you call sql server from vb through ado, does it pass back just the time component...with no conversion function?|||I wonder if there is a magic time, that just returns the date...

Why the original developer didn't use CONVERT is betond me...|||Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.|||SELECT CONVERT(datetime,-2,101)
gives '1899-12-30 00:00:00.000'|||Originally posted by rnealejr
Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.

What does that mean?

Datetime is stored as a number...4 before the decimal, 4 after...

What do you mean time only?|||So what does this prove?

SELECT DATEADD(d,1,CONVERT(datetime,0.1))

Add 1 what?

What do you mean with no date? There's always a date component?

0 is the default

(Unless of course you add through EM then it's -2)

huh?

And why, if you make a sql call from vb, and the date is 1899-12-30, it only returns the time..VERY bizzare|||Just had the developer do it from an Excel workbook too.

It puts just the time in the Cell with that 1899-12-03 date

It doesn't return the date...bizzaro

Anyone else seen this?|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27101

Hey Brett ... some more with excel problems ...

I believe the reason is that while SQL takes the default date to be 1900-01-01

and other MS applications use 1899-12-30

Here is a link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/whatisdatehowdiditgetthere.asp|||Thanks...forgot all about that thread...

Still doesn't explain you only get the decimal portion of the datetime field though (that's the time component)