sql server - How SQL's convert function work when converting datetime to float?

Question

why the out put of this query:

declare @currentDate as datetime
    set @currentDate ='01/07/2010'

select convert(float, @currentdate) 

...is 40183 ?

So for those who are getting confuse with my question, my question is How to know the result of above query without executing it ?

Answer 1

DateTime is often represented as a day count from a pre-determined date (generally know as the epoch) on the integer part and the percentage of the day elapsed since mid-night on the fractional part.

SQL Server is not the exception to this, thus the conversion to Float makes a lot of sense. Day 0 is Jan 01 1900 00:00:00 (AFAIK, in no particular time-zone, so you shall consider it "local time").

So, you can try this:

declare @ADate DateTime;
set @ADate = '19000101 00:00:00';
select CONVERT(float, @ADate);  --should print 0
set @ADate = '19000101 12:00:00';
select CONVERT(float, @ADate);  --should print 0.5
set @ADate = '19001231 06:00:00';
select CONVERT(float, @ADate);  --should print 364.25

So, for your results, 40183 days has been passed since 01/01/1900 00:00:00 and 01/07/2010 00:00:00

Clarification: Unix like systems use a different approach to store datetimes: Seconds since Unix epoch (Jan 1 1970 00:00:00 UTC), which is more known as epoch time.

[Edit] Date format on this response was changed to YYYYMMDD format on 20140416, after some new years of experience with SQL Server (and as @Damien said in his comment) this is the only safe format.