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;