What will the results be for the code below?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by UMG Developer » What will the results be for the code below?

Add to briefcase

24 posts, Page 2 of 3

What will the results be for the code below?

Expand / Collapse

Author

Message

Posted Monday, November 01, 2010 10:25 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010

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 01, 2010 12:46 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, June 10, 2013 2:43 PM
Points: 216, Visits: 111

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 01, 2010 1:49 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 1,063, Visits: 4,258

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 01, 2010 6:01 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, June 16, 2013 9:23 AM
Points: 2,580, Visits: 7,292

Toreador (11/1/2010)

Good question and I learnt something Smile

Smile

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

I did

BigGrin

.... I've got my share wrong too. Crying

Crying

Post #1014213

Christian Buettner-167247

Posted Tuesday, November 02, 2010 2:00 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 2,547, Visits: 3,648

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 02, 2010 3:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 1,384, Visits: 4,879

Christian Buettner-167247 (11/2/2010)

Hm, am I the only one who thinks the given answer is false?

Almost certainly Wink

Wink

Post #1014338

L' Eomot Inversé

Posted Tuesday, November 02, 2010 8:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 7,182, Visits: 7,281

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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware

Post #1014512

Posted Tuesday, November 02, 2010 9:43 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 2,163, Visits: 2,150

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 02, 2010 2:27 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 18,856, Visits: 12,440

Good question - thanks

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

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 04, 2010 11:43 AM

Forum 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

24 posts, Page 2 of 3

Permissions

Expand / Collapse