DateTime Puzzle

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    Comments posted to this topic are about the item DateTime Puzzle

  • Ron McCullough

    SSC Guru

    Points: 63877

    Thanks, a nice easy question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Paul White

    SSC Guru

    Points: 150442

    The explanation could be better. The question does not involve a float value. By default, the literal value 0.25 is interpreted as a numeric(2,2):

    SELECT

    0.25 AS col1

    INTO #v;

    EXECUTE

    tempdb.sys.sp_columns

    @table_name = N'#v',

    @table_owner = N'banana',

    @table_qualifier = N'tempdb',

    @column_name = N'col1';

    DROP TABLE #v;

    If the intention has been to show a float, either a float literal or a typed variable could have been used:

    SELECT CAST(25e-2 AS datetime);

    DECLARE @f float = 25e-2;

    SELECT CAST(@f AS datetime);

    Datetime values are no more "associated" with a float value than they are with any other type that can be implicitly converted (see the conversion table in Books Online - CAST and CONVERT (Transact-SQL)). The internal representation is two integers - one for the number of days from the base date, and one for the number of ticks (1/300th second) since midnight. In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

    DECLARE @dt datetime = '1900-01-02 00:00:00.006';

    SELECT @dt;

    SELECT CONVERT(binary(8), @dt);

    My biggest concern with this question though, is that it encourages people to be sloppy with types and relies on hidden implicit conversions. As a general rule, try to be explicit about types in T-SQL code.

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Thanks for the question. Easy one to start the week.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Thanks, Paul! You saved me a lot of time by addressing all the points I wanted to address after reading the explanation of this question.

    It never ceases to amaze me how many people think datetime data is internally stored as a float. That statement is even being made explicitly in the topic referenced in the explanation of this question. And while this question uses the weaker verb "associate", it will again reinforce that believe for some people.

    And now, I am silently hoping that we'll get the same question tomorrow, with the same answer options - but with data type datetime2. <evil grin>


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    The answer was easy.

    I also appreciate the discussion from SQL Kiwi.

    Thanks

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    The answer was easy.

    I also appreciate the discussion from SQL Kiwi.

    Thanks

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    The answer was easy.

    I also appreciate the discussion from SQL Kiwi.

    Thanks

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • This was removed by the editor as SPAM

  • Paul White

    SSC Guru

    Points: 150442

    Hugo Kornelis (5/7/2012)


    Thanks, Paul! You saved me a lot of time by addressing all the points I wanted to address after reading the explanation of this question.

    Makes a change! I normally get to the question after you and end up posting the '+1' 🙂

    I too, look forward to the repeat question based on DATETIME2.

  • Thomas Abraham

    SSChampion

    Points: 10761

    SQL Kiwi (5/5/2012)

    Datetime values are no more "associated" with a float value than they are with any other type that can be implicitly converted (see the conversion table in Books Online - CAST and CONVERT (Transact-SQL)). The internal representation is two integers - one for the number of days from the base date, and one for the number of ticks (1/300th second) since midnight. In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

    DECLARE @dt datetime = '1900-01-02 00:00:00.006';

    SELECT @dt;

    SELECT CONVERT(binary(8), @dt);

    My biggest concern with this question though, is that it encourages people to be sloppy with types and relies on hidden implicit conversions. As a general rule, try to be explicit about types in T-SQL code.

    Thanks for the illuminating post. The "SELECT CONVERT(binary(8), @dt);" was most interesting, as I had never thought of explicitly showing the representation of date values that way.

    Thanks to OP for the question. A nice easy one for Monday.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    SQL Kiwi (5/5/2012)In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

    Which would lead me to believe that each "tick" in the time integer is actually 3 1/3 ms and not 3.3 ms, which makes sense regarding the rounding.

  • SQLRNNR

    SSC Guru

    Points: 281210

    SQL Kiwi (5/5/2012)


    The explanation could be better. The question does not involve a float value. By default, the literal value 0.25 is interpreted as a numeric(2,2):

    SELECT

    0.25 AS col1

    INTO #v;

    EXECUTE

    tempdb.sys.sp_columns

    @table_name = N'#v',

    @table_owner = N'banana',

    @table_qualifier = N'tempdb',

    @column_name = N'col1';

    DROP TABLE #v;

    If the intention has been to show a float, either a float literal or a typed variable could have been used:

    SELECT CAST(25e-2 AS datetime);

    DECLARE @f float = 25e-2;

    SELECT CAST(@f AS datetime);

    Datetime values are no more "associated" with a float value than they are with any other type that can be implicitly converted (see the conversion table in Books Online - CAST and CONVERT (Transact-SQL)). The internal representation is two integers - one for the number of days from the base date, and one for the number of ticks (1/300th second) since midnight. In fact the second format seems to be 0.003 second time intervals, rounded to 0, 3, or 7 in the third decimal place when used:

    DECLARE @dt datetime = '1900-01-02 00:00:00.006';

    SELECT @dt;

    SELECT CONVERT(binary(8), @dt);

    My biggest concern with this question though, is that it encourages people to be sloppy with types and relies on hidden implicit conversions. As a general rule, try to be explicit about types in T-SQL code.

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Revenant

    SSC-Forever

    Points: 42467

    A 5-second question... Thanks!

  • Lon-860191

    SSCrazy

    Points: 2449

    Thanks, an easy way to start this week.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply