Union Date/Time Data Types

  • David Fundakowski

    Ten Centuries

    Points: 1290

    Comments posted to this topic are about the item Union Date/Time Data Types

  • sknox

    SSChampion

    Points: 12215

    The correct answer is "it depends."

    MOST of the time you'll get two rows as explained in the answer.

    HOWEVER, each GETDATE() within each SELECT is evaluated separately. When the separate evaluations cross a 3-millisecond boundary, you'll get 3 rows instead of 2.

    To test this, run the code with GO 100 after it in SSMS, and look at the total result count. You may have to run this multiple times to cross a boundary, but sometimes the row count will be greater than the expected 200.

    Theoretically, it's possible to get 4 or 5 rows, but you'd need an incredibly slow or stressed system to take over 3 milliseconds (crossing at least two 3-millisecond boundaries) to evaluate 5 GETDATE() calls.

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    If you let this run for a while you will eventually get 1 row back from the query.

    declare @T table(Value datetime2);

    declare @I int = 0

    while 1 = 1

    begin

    set @I += 1;

    insert into @T(value)

    SELECT GETDATE()

    UNION

    SELECT CAST(GETDATE() AS DATETIME2)

    UNION

    SELECT CAST(GETDATE() AS DATETIME)

    UNION

    SELECT CAST(GETDATE() AS SMALLDATETIME)

    UNION

    SELECT CAST(GETDATE() AS DATETIME2(7))

    if @@rowcount < 2

    --if @@rowcount > 2

    break;

    delete from @T;

    end

    select @I;

    select *

    from @T;

    The check for more than two will finish much faster.

  • Rune Bivrin

    SSCertifiable

    Points: 7536

    In addition to the previous, the padding has changed on SQL 2016. It will pad with 0000, 3333 or 6667, depending on the value of the DATETIME. I suppose that is to reflect the fact that DATETIME isn't precise down to a single millisecond.


    Just because you're right doesn't mean everybody else is wrong.

  • paul.knibbs

    SSCoach

    Points: 15270

    Mikael Eriksson SE (8/25/2016)


    If you let this run for a while you will eventually get 1 row back from the query.

    Yeah, I was going to say that ought to be possible, because if the only difference between the SMALLDATETIME and regular DATETIME is that the former drops seconds, you'll end up with an identical value if GETDATE() happens to have no seconds in it when that conversion is done. Would have been better to use a specific DATETIME value rather than GETDATE() for this question.

  • octav 25679

    Valued Member

    Points: 55

  • sipas

    Hall of Fame

    Points: 3064

    sknox (8/25/2016)


    The correct answer is "it depends."

    MOST of the time you'll get two rows as explained in the answer.

    HOWEVER, each GETDATE() within each SELECT is evaluated separately. When the separate evaluations cross a 3-millisecond boundary, you'll get 3 rows instead of 2.

    To test this, run the code with GO 100 after it in SSMS, and look at the total result count. You may have to run this multiple times to cross a boundary, but sometimes the row count will be greater than the expected 200.

    Theoretically, it's possible to get 4 or 5 rows, but you'd need an incredibly slow or stressed system to take over 3 milliseconds (crossing at least two 3-millisecond boundaries) to evaluate 5 GETDATE() calls.

    +1

  • tom.w.brannon

    Hall of Fame

    Points: 3946

    sipas (8/26/2016)


    sknox (8/25/2016)


    The correct answer is "it depends."

    MOST of the time you'll get two rows as explained in the answer.

    HOWEVER, each GETDATE() within each SELECT is evaluated separately. When the separate evaluations cross a 3-millisecond boundary, you'll get 3 rows instead of 2.

    To test this, run the code with GO 100 after it in SSMS, and look at the total result count. You may have to run this multiple times to cross a boundary, but sometimes the row count will be greater than the expected 200.

    Theoretically, it's possible to get 4 or 5 rows, but you'd need an incredibly slow or stressed system to take over 3 milliseconds (crossing at least two 3-millisecond boundaries) to evaluate 5 GETDATE() calls.

    +1

    agreed. Just my luck that first time I ran it 3 rows were returned.

  • Toni-256719

    SSCarpal Tunnel

    Points: 4859

    I agree with "it depends" as a better answer.

    I got 3 rows back so I got it wrong. 😉

  • Lee Nadelman

    SSCertifiable

    Points: 5320

    I agree on "it depends". I got 3 rows when I ran the query.

  • TomThomson

    SSC Guru

    Points: 104762

    Not the best possible question, as there are either several correct answers (and not all correct anwers are options) or no correct answers, depending on how one interprets it.

    If the question had been "how many rows is the following query most likely to return" there would have been exactly one correct answer.

    Tom

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    sipas (8/26/2016)


    sknox (8/25/2016)


    The correct answer is "it depends."

    MOST of the time you'll get two rows as explained in the answer.

    HOWEVER, each GETDATE() within each SELECT is evaluated separately. When the separate evaluations cross a 3-millisecond boundary, you'll get 3 rows instead of 2.

    To test this, run the code with GO 100 after it in SSMS, and look at the total result count. You may have to run this multiple times to cross a boundary, but sometimes the row count will be greater than the expected 200.

    Theoretically, it's possible to get 4 or 5 rows, but you'd need an incredibly slow or stressed system to take over 3 milliseconds (crossing at least two 3-millisecond boundaries) to evaluate 5 GETDATE() calls.

    +1

    +1

  • webrunner

    One Orange Chip

    Points: 29863

    Great question when combined with the discussion here. Aside from the recommendations about how to improve the question itself, I learned a lot from this example overall.

    Thanks!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Revenant

    SSC-Forever

    Points: 42467

    Great discussion, thanks to everybody, especially to David.

  • pstanislav

    SSC Eights!

    Points: 939

    I agree with the comments here - the code as written will produce variable results as the separate GETDATE functions are evaluated. This can be avoided by getting and holding the current time and using that variable instead:

    DECLARE @CurrDtTime AS DATETIME = GETDATE();

    SELECT @CurrDtTime

    UNION

    SELECT CAST(@CurrDtTime AS DATETIME2)

    UNION

    SELECT CAST(@CurrDtTime AS DATETIME)

    UNION

    SELECT CAST(@CurrDtTime AS SMALLDATETIME)

    UNION

    SELECT CAST(@CurrDtTime AS DATETIME2(7))

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

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