Storage of DATETIME column

Did the storage of DATETIME in SQL Server change from being a FLOAT to a new data type?
more ▼

asked Jun 09, 2011 at 06:37 AM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

@Raj More It has never been a float in any version. I pimped the link to my script in the comment to @Kevin Feasel's answer, it shows how datetime values are stored.
Jun 09, 2011 at 07:54 AM Oleg

@Oleg: you're right. I deleted my answer to reduce any confusion (not just to cover up when I'm wrong...err...).

The link that @Oleg mentioned was http://ask.sqlservercentral.com/questions/16420/php-with-mssql-strtotime-with-mssql-datetime-column.
Jun 09, 2011 at 08:00 AM Kevin Feasel
@Kevin Feasel This was a good answer as it has shown that it is possible to convert the float to datetime. The only difference is that in this case the digits before the decimal point (the float value cast as int) go to the left int (number of days) and the digits after the decimal point are used to calculate the ticks past midnight (([float value] - [float value cast as int]) * max_ticks cast as int and then fed to the right int)
Jun 09, 2011 at 09:12 AM Oleg
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Are you concerned about implicit and explicit conversions, and if they've changed from an older version of SQL?

The conversion charts are shown at http://msdn.microsoft.com/en-us/library/ms187928.aspx for SQL 2008 R2, and http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx for SQL 2000. Doesn't seem to show any differences between the way dates are converted to floats, though.

@Raj More - what's the actual problem?
more ▼

answered Jun 09, 2011 at 06:55 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left

DATETIME is stored as 8 bytes: http://msdn.microsoft.com/en-us/library/ms187819.aspx

FLOAT is a datatype and is stored in either 4 or 8 bytes : http://msdn.microsoft.com/en-us/library/ms173773.aspx

Are you getting datatypes and storage mixed up?
more ▼

answered Jun 09, 2011 at 06:45 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

The storage if DATETIME is, like all other data types, a number of bytes. For a DATETIME it's eight bytes, where I think the first four bytes store the number of days since January 1st 1900 and the other four bytes are the number of milliseconds since midnight.

more ▼

answered Jun 09, 2011 at 06:49 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 19 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 09, 2011 at 06:37 AM

Seen: 1232 times

Last Updated: Jun 09, 2011 at 06:37 AM