DateTime Puzzle

  • Comments posted to this topic are about the item DateTime Puzzle

  • 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]

  • 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.

  • 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

  • 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/

  • The answer was easy.

    I also appreciate the discussion from SQL Kiwi.

    Thanks

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • The answer was easy.

    I also appreciate the discussion from SQL Kiwi.

    Thanks

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • A 5-second question... Thanks!

  • Thanks, an easy way to start this week.

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

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