Zero date.
-
I did understand that the datetime in SQL-server is stored as the number of microseconds / 3 since 1899-12-30.
If a do a cast I get the more logical result.
select '--' [--], CAST(0 as datetime) as Zero_time
-- Zero_time
---- -----------------------
-- 1900-01-01 00:00:00.000
In the predesessor of the Management Studio, the since date was sometimes still 'visible'.
Questions:
What has changed?
Does the cast correct for the two days of difference?
(I thought that the cast did a 'binairy' conversion. False?).
Can the since date be made visible?
Ben
-
ben.brugman (11/29/2013)
I did understand that the datetime in SQL-server is stored as the number of microseconds / 3 since 1899-12-30.No. Never has been.
Datetime is made up of two pieces. The first is the number of days since 1900-01-01. The second is the number of clock ticks since midnight. Each clock tick is 1/300 seconds. So the 0 date has always been 1900-01-01 00:00:00.000 since there was a SQL Server and probably before (when it was still sybase)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ben.brugman (11/29/2013)
I did understand that the datetime in SQL-server is stored as the number of microseconds / 3 since 1899-12-30.If a do a cast I get the more logical result.
select '--' [--], CAST(0 as datetime) as Zero_time
-- Zero_time
---- -----------------------
-- 1900-01-01 00:00:00.000
In the predesessor of the Management Studio, the since date was sometimes still 'visible'.
Questions:
What has changed?
Does the cast correct for the two days of difference?
(I thought that the cast did a 'binairy' conversion. False?).
Can the since date be made visible?
Ben
To add to what Gail has stated, the "0" date for SQL Server has always been 1900-01-01 00:00:00.000. IIRC, VB has a base date of the last date of 1899. Excel is still kind of screwy because they never actually fixed the problem of Excel thinking that 1900 was a leap year. They came up with a work around but the didn't actually fix the underlying problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Books online has a nice topic on datetime http://technet.microsoft.com/en-us/library/ms187819(v=sql.105).aspx
Default value
1900-01-01 00:00:00
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply