November 25, 2014 at 12:59 pm
My favourite question in a long time! Thanks!
November 25, 2014 at 2:00 pm
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;
November 25, 2014 at 4:15 pm
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).
November 25, 2014 at 9:54 pm
Good to know, nice question & explanation.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 25, 2014 at 11:27 pm
Nice question, Thanks for sharing
November 26, 2014 at 5:26 am
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.
November 26, 2014 at 7:16 am
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
November 26, 2014 at 7:45 am
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
November 26, 2014 at 7:52 am
Easy one, thanks.
December 1, 2014 at 2:53 pm
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)
You must be logged in to reply to this topic. Login to reply