# In The Beginning

• My favourite question in a long time! Thanks!

• kherald69 (11/25/2014)

Ed Wagner (11/25/2014)

PHYData DBA (11/25/2014)

Brian.Klinect (11/25/2014)

That's exactly what I did. I had no idea I could use a negative number for a datetime!

Since DateTime is stored as an Integer, it only makes sense to use the Positive and Negative Values.

Anything thing else would be wasteful.

Actually, it's stored as two integers. The first one is the date and the second one is the time. I completely agree that not using the full range of the date integer would be wasteful. It's actually pretty efficient and works well.

Actually, it is stored as a float. The whole number is the date and the fraction is the time.

Then why does this work?

`WITH cte AS (`

` SELECT GETDATE() date_format,`

` CAST(GETDATE() AS Binary(8)) binary_format)`

`SELECT date_format now_datetime,`

` binary_format now_binary,`

` SUBSTRING(binary_format, 1, 4) day_part_bin,`

` SUBSTRING(binary_format, 5, 4) time_part_bin,`

` CAST(SUBSTRING(binary_format, 1, 4) AS Integer) date_part_int,`

` CAST(SUBSTRING(binary_format, 5, 4) AS Integer) time_part_int,`

` DATEADD(DAY, CAST(SUBSTRING(binary_format, 1, 4) AS Integer), 0) today_no_time`

` FROM cte;`

• I can see that it works but what is the meaning of the time int value.

Here is an example of why I say it is stored as a float.

`with cte as (`

` select getdate() as date_format`

` , cast(getdate() as float) as date_as_float)`

`select date_format`

` , date_as_float`

` , floor(date_as_float) as date_int`

` , date_as_float - floor(date_as_float) as time_fraction`

` , floor((date_as_float - floor(date_as_float)) * 86400000) as time_in_ms`

`from cte;`

If you take the time_in_ms and do the math you can get the hours, minutes, seconds and milliseconds (+/- a few ms).

• Good to know, nice question & explanation.

• Nice question, Thanks for sharing

• kherald69 (11/25/2014)

I can see that it works but what is the meaning of the time int value.

Here is an example of why I say it is stored as a float.

`with cte as (`

` select getdate() as date_format`

` , cast(getdate() as float) as date_as_float)`

`select date_format`

` , date_as_float`

` , floor(date_as_float) as date_int`

` , date_as_float - floor(date_as_float) as time_fraction`

` , floor((date_as_float - floor(date_as_float)) * 86400000) as time_in_ms`

`from cte;`

If you take the time_in_ms and do the math you can get the hours, minutes, seconds and milliseconds (+/- a few ms).

The second integer is the number of 0.003 second timeslices since midnight.

• Carlo Romagnano (11/25/2014)

Extremely easy!

Thanks!

But more than half the answers so far were wrong! (Probably because people forgot about negative integers, I guess.)

Tom

• Kev T (11/25/2014)

Thanks Paul, how interesting! I wonder how this is handled in Greece with regards to big data and historical records, or if they forgone the lost days somehow.

I doubt if Greece has any problems when compared to many other places.

For example in India the official calendars (note the plural) are (i) the Indian National Calendar (a lunar calendar) and (ii) the Gregorian Calendar, and neither is used for anything much by Indians, who mostly use the Vikram Samvat calendar (another lunar one, where most years are a day or two less than 360 days, but about every 5 or 6 years there will be a year 29 or 30 days longer than a typical year to bring the calendar back into line with the seasons).

Israel uses both the Gregorian and the Hebrew calendar.

There are at least 5 distinct calendars in use in the Muslim world (Hijri, Hijri-Shamsi, Oromo, Ethiopic, and Persian).

And just to create some real fun, earlier this year the Orthodox Church of Poland switched back to the unrevised Julian Calendar, having since 1924 used the revised Julian calendar of 1923, while of course the Polish government is on the Gregorian calendar (presumably the Government and the Orthodox church will now celebrate Christmas on different days).

Tom

• Easy one, thanks.

• TomThomson (11/26/2014)

Carlo Romagnano (11/25/2014)

Extremely easy!

Thanks!

But more than half the answers so far were wrong! (Probably because people forgot about negative integers, I guess.)

Forgot about, and hadn't used, but conceptually I would have missed it also. I would expect the beginning of time to be zero rather than the smallest date able to be entered. Like if height were stored as a float.

But I learned something new... 🙂

Viewing 10 posts - 31 through 39 (of 39 total)