Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

What will the results be for the code below? Expand / Collapse
Author
Message
Posted Monday, November 1, 2010 10:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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.
Post #1013945
Posted Monday, November 1, 2010 12:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:09 AM
Points: 216, Visits: 118
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
Post #1014044
Posted Monday, November 1, 2010 1:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
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.
Post #1014088
Posted Monday, November 1, 2010 6:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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 .... I've got my share wrong too.



Post #1014213
Posted Tuesday, November 2, 2010 2:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #1014318
Posted Tuesday, November 2, 2010 3:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,713, Visits: 6,250
Christian Buettner-167247 (11/2/2010)
Hm, am I the only one who thinks the given answer is false?


Almost certainly
Post #1014338
Posted Tuesday, November 2, 2010 8:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,563, Visits: 9,063
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
Post #1014512
Posted Tuesday, November 2, 2010 9:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #1014632
Posted Tuesday, November 2, 2010 2:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 21,251, Visits: 14,958
Good question - thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1014889
Posted Thursday, November 4, 2010 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 6:30 AM
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
Post #1016089
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse