locked
Convert datetime to a number? RRS feed

  • Question

  • Hi,

    I try this:

    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.451') as float) --41007,3442760417   
    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.450') as float) --41007,3442760417

    But this returns the same number...can't I get a number that is different for every possible datatime value?

    --
    Werner


    Friday, April 27, 2012 9:21 AM

Answers

  • Those two string datetime values map to the same internal datetime value.  Try these:

    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.451') as float) --41007,3442760417   
    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.454') as float) --41007.3442760803

    See how datetime represented internally. You may get surprised:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, April 27, 2012 9:31 AM
  • Hello Werner,

    A datetime can only represent 3/100 sec, means and 08:15:45.451 and 08:15:45.450 are equal datetime values:

    SELECT convert(datetime, '2012-04-10 08:15:45.451') 
    SELECT convert(datetime, '2012-04-10 08:15:45.450')
    SELECT convert(datetime, '2012-04-10 08:15:45.452')
    The 2 first return .450, the second .453


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    Friday, April 27, 2012 9:33 AM
  • This is documented in the DATETIME description, see paragraph Rounding of datetime Fractional Second Precision.
    Friday, April 27, 2012 9:35 AM

All replies

  • Those two string datetime values map to the same internal datetime value.  Try these:

    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.451') as float) --41007,3442760417   
    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.454') as float) --41007.3442760803

    See how datetime represented internally. You may get surprised:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, April 27, 2012 9:31 AM
  • Hello Werner,

    A datetime can only represent 3/100 sec, means and 08:15:45.451 and 08:15:45.450 are equal datetime values:

    SELECT convert(datetime, '2012-04-10 08:15:45.451') 
    SELECT convert(datetime, '2012-04-10 08:15:45.450')
    SELECT convert(datetime, '2012-04-10 08:15:45.452')
    The 2 first return .450, the second .453


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    Friday, April 27, 2012 9:33 AM
  • This is documented in the DATETIME description, see paragraph Rounding of datetime Fractional Second Precision.
    Friday, April 27, 2012 9:35 AM
  • The problem lies in the CONVERT function, run this:

    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.451') as datetime) --41007,3442760417  
    SELECT CAST(convert(datetime, '2012-04-10 08:15:45.450') as datetime) --41007,3442760417

    and it will return the original date (2012-04-10 08:15:45.450), I think you need to provide a certain format code with the CONVERT function so it will detect that final digit as a ms and not a hundredth of a second!

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    21 or 121 should do it!

    Friday, April 27, 2012 9:43 AM
  • Hi Werner ,

    SELECT

    replace(replace(replace(convert(VARCHAR(MAX), CAST('2012-04-10 08:15:45.451' AS DATETIME),120),'-',''),' ',''),':','')

    OR

    SELECT

    CAST(replace(replace(replace(convert(VARCHAR(MAX), CAST('2012-04-10 08:15:45.451' AS DATETIME),120),'-',''),' ',''),':','') AS FLOAT )...

    Hope it will help you...

    Please mark it as answer if it helpful to you...

    Friday, April 27, 2012 9:51 AM
  • Hi Werner ,

    SELECT

    replace(replace(replace(convert(VARCHAR(MAX), CAST('2012-04-10 08:15:45.451' AS DATETIME),120),'-',''),' ',''),':','')

    OR

    SELECT

    CAST(replace(replace(replace(convert(VARCHAR(MAX), CAST('2012-04-10 08:15:45.451' AS DATETIME),120),'-',''),' ',''),':','') AS FLOAT )...

    Hope it will help you...

    Please mark it as answer if it helpful to you...

    Thanks, but this does not change the precision of the datetime data type - which was my problem :). However others might use your answer to obtain a number without the fraction part.

    --
    Werner

    Friday, April 27, 2012 9:59 AM