Union Date/Time Data Types
-
Comments posted to this topic are about the item Union Date/Time Data Types
-
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.
-
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.
-
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. -
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.
-
-
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
-
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.
-
I agree with "it depends" as a better answer.
I got 3 rows back so I got it wrong. 😉
-
I agree on "it depends". I got 3 rows when I ran the query.
-
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
-
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
-
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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Great discussion, thanks to everybody, especially to David.
-
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 19 total)
You must be logged in to reply to this topic. Login to reply