SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will the results be for the code below?


What will the results be for the code below?

Author
Message
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1958 Visits: 3059
The good thing is that I learned somethng here, and not about conversion precedence.

I answered that the intial assignment would fail as the CONVERT function seemed wrong. The third parameter, 101 in this case, is optional for converting strings to datetime but could be necessary to resolve ambiguities when some dates are presented in formats other than the db's local standard. I'd wrongly thought that the third parameter only worked for converting datetime TO a string.
timothy bates
timothy bates
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 124
Nice QOTD.

I got it right but for different reasons. If you wanted to add an additional wrinkle try:
SELECT @Var2 = ' - ', @Var3 = CONVERT(DATETIME, '01/01/2010', 101),@Var1 = cast(@var3+5 as int);

SELECT @Var1 + @Var2 + @Var3 AS Result1;
If the perception is that this is performing date addition (e.g. Dateadd) the above expected result would be 5, not 2120-01-07 00:00:00.00.

Tim
jts2013
jts2013
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 5009
A very good explantation - thanks. I got it right with a bit of guesswork, but hadn't realised that the operator precedence was the key.
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4783 Visits: 9108
Toreador (11/1/2010)
Good question and I learnt something :-)

I wonder how many of those who got it right (70% at the moment) did so without running the script first!

I did :-D .... I've got my share wrong too. Crying



Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3547 Visits: 3889
Hm, am I the only one who thinks the given answer is false?
According to it, there will be a "type conversion". But instead there is only a "type conversion error". Two different things...

Best Regards,

Chris Büttner
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2758 Visits: 8083
Christian Buettner-167247 (11/2/2010)
Hm, am I the only one who thinks the given answer is false?


Almost certainly ;-)
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14208 Visits: 12197
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
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2664 Visits: 2204
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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31946 Visits: 18551
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

Carto Query
Carto Query
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 33
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search