Union Date/Time Data Types

Question

Union Date/Time Data Types

Viewing 5 posts - 16 through 19 (of 19 total)

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

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.

Good catch.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

pstanislav (8/26/2016)
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))

+1.

But even then the answer is not certain.

If you happen to run the query on an exact minute it will return 1 record.

Try this:

DECLARE @CurrDtTime AS DATETIME = DATEADD(n, DATEDIFF(n, 0, GETDATE()), 0);

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65141 More actions · Answer 3

Notwithstanding the above comments, I think using a static datetime value would have been better to remove ambiguity.

Also there is no mention in the answer of the datatype precedence in the union as all the values would be converted to DATETIME2(7) prior to the union.

Far away is close at hand in the images of elsewhere.
Anon.

hungphan88 SSC Rookie Points: 37 More actions · Answer 4

hungphan88

SSC Rookie

Points: 37

September 5, 2016 at 10:28 pm

#1899246

It's right. I got 3 rows after run this query

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Interesting question, but flawed execution. Thanks.

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