Zero date.

Question

Zero date.

ben.brugman

SSChampion

Points: 13369
More actions
November 29, 2013 at 5:45 am

#301913

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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