14

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 ?

Jeevan Bhatt
  • 5,881
  • 18
  • 54
  • 82

4 Answers4

13

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.

jachguate
  • 16,976
  • 3
  • 57
  • 98
  • about your date format - the safe way to specify a date and time is to do YYYY-MM-DD, and to use a 'T' instead of a space, to separate the date and time components (just FYI really) – Damien_The_Unbeliever Nov 10 '10 at 07:41
  • @Damien_The_Unbeliever, I used to work with YYYY-MM-DD date format on all major database engines... but (believe it or not) MS-SQL Express 2008 in a Windows 7 Business 32bit Spanish, wants it in YYYY-DD-MM format!! (just FYI really) :D – jachguate Nov 10 '10 at 21:26
  • if it's just a date, then YYYYMMDD is the only safe, unambiguous, format (no dashes). Of course, if you use datetime2 or the other newer types, then all of the rules change yet again :-) – Damien_The_Unbeliever Nov 11 '10 at 07:11
5

DateTime values are actually stored as two four-byte integers under the hood. The first four-byte integer represents the number of days since 1900-01-01. The second four-byte integer stores the number of milliseconds since midnight. When you convert a datetime into a float, the decimal portion represents the percentage of the 24-day that has past. Thus, 0.5 represents noon.

Thomas
  • 63,911
  • 12
  • 95
  • 141
3

It's basically converting the datetime to an OLE Date. There's a decent description of the process in the documentation for System.DateTime.ToOADate():

http://msdn.microsoft.com/en-us/library/system.datetime.tooadate.aspx

The quick explanation is that the integer part is the number of days since 12/30/1899. The fractional part (zero in this case) is the time divided by 24.

Brannon
  • 25,687
  • 5
  • 39
  • 44
1

This should help you understand the TSQL implementation (or implement your own):

DECLARE
    @date DATETIME = '20180125 09:15:30.549',
    @date_dec DECIMAL (26,10) = 43123.3857702546

SELECT
    CAST(@date_dec AS DATETIME) AS [TSQL cast to DATETIME],
    CAST(@date AS DECIMAL (26,10)) AS [TSQL cast to DECIMAL]

SELECT
    DATEADD(DAY, FLOOR(@date_dec), 
        DATEADD(HOUR, FLOOR(@date_dec % 1 * 24), 
            DATEADD(MINUTE, FLOOR((@date_dec % 1 * 24) % 1 * 60), 
                DATEADD(SECOND, FLOOR(((@date_dec % 1 * 24) % 1 * 60) % 1 * 60), 
                    DATEADD(MILLISECOND, FLOOR((((@date_dec % 1 * 24) % 1 * 60) % 1 * 60) % 1 * 1000), '19000101')
                )
            )
        )
    ) AS [Manual cast to DATETIME],
    DATEDIFF(DAY, '19000101', @date) 
    + (
        DATEPART(HOUR, @date) 
        + (
            DATEPART(MINUTE, @date) 
            + (
                DATEPART(SECOND, @date)
                + DATEPART(MILLISECOND, @date) / CAST(1000 AS FLOAT)
              ) / CAST(60 AS FLOAT)
          ) / CAST(60 AS FLOAT)
      ) / CAST(24 AS DECIMAL (26,10)) AS [Manual cast to DECIMAL]

Note that the result is not always the same as TSQL loses precision on last millisecond digit.

Vedran
  • 10,369
  • 5
  • 50
  • 57