What will the results be for the code below?

Question

Post reply

What will the results be for the code below?

Viewing 10 posts - 16 through 24 (of 24 total)

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

Toreador SSChampion Points: 11358 More actions · Answer 1

Christian Buettner-167247 (11/2/2010)
Hm, am I the only one who thinks the given answer is false?

Almost certainly 😉

TomThomson SSC Guru Points: 104773 More actions · Answer 2

A fun question, that suggests less than fortunate consequences of SQL's bizarre implicit type conversion and type precedence rules. In SQL (unlike almost every other programming language) it is NOT true that the binary operator "+" is associative in the absence of overflow.

Tom

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Christian Buettner-167247 (11/2/2010)
According to it, there will be a "type conversion". But instead there is only a "type conversion error". Two different things...

I swear I had error in there, sorry about that. But I submitted the question about four months ago so I'm not sure my memory is completely accurate.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Good question - thanks

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

Carto Query Newbie Points: 7 More actions · Answer 5

Hi there,

Newbie question alert! I was surprised to see datetime still defining the time, given that style 101 was named. Shouldn't this display only the mm/dd/yyyy?

I was referencing: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Thanks.

Tammy

UMG Developer SSChampion Points: 13482 More actions · Answer 6

tsikma (11/4/2010)
Newbie question alert! I was surprised to see datetime still defining the time, given that style 101 was named. Shouldn't this display only the mm/dd/yyyy?

The CONVERT/101 was only referenced in setting the @Var3 variable which is of type DATETIME. The SELECT statement that returns results doesn't use convert so it uses the default format for your language in displaying the value which includes the time portion.

Peter Petrov SSCommitted Points: 1516 More actions · Answer 7

Actually the explanation of the behavior of the fist SELECT statement is not very accurate:

"because DATETIME has a higher precedence than INT 2 is converted to a DATETIME which results in '1900-01-03 00:00:00.000' so the end result is '2010-01-03 00:00:00.000'."

In fact with "+" and "-" arithmetic operations the datetime values convert to numeric and after the operation they convert back to datetime. Otherwise it's very hard to define "addition" and "subtraction" between dates :-D. Run the following script:

DECLARE@Var0 datetime

,@Var1 datetime

,@Var2 datetime

,@Var3 datetime;

SELECT@Var0 = '01/01/2010'

,@Var1 = '1900-01-03 00:00:00.000'

,@Var2 = '1900-01-03 11:00:00.000'

,@Var3 = 2;

SELECT CAST(CAST(@Var1 AS decimal(38,19)) + CAST(@Var0 AS decimal(38,19)) AS datetime);

SELECT CAST(@Var1 AS decimal(38,19)), CAST(@Var0 AS decimal(38,19));

SELECT CAST(CAST(@Var2 AS decimal(38,19)) + CAST(@Var0 AS decimal(38,19)) AS datetime);

SELECT CAST(@Var2 AS decimal(38,19)), CAST(@Var0 AS decimal(38,19));

SELECT CAST(CAST(@Var3 AS decimal(38,19)) + CAST(@Var0 AS decimal(38,19)) AS datetime);

SELECT CAST(@Var3 AS decimal(38,19)), CAST(@Var0 AS decimal(38,19));

Carto Query Newbie Points: 7 More actions · Answer 8

Thanks very much. Wasn't aware of the hierarchy of types. Always good to end a day with something new between the ears. 🙂

Ameya- Ameyask SSC Veteran Points: 203 More actions · Answer 9

Excellent qustion. I was not aware of this. Got to learn something new... of course by choosing wrong answer 😀

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

Good question, though I'm a little late to the party.