Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 What will the results be for the code below? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 4, 2010 11:51 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 30, 2015 9:47 AM Points: 2,160, Visits: 2,204
 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.
Post #1016096
 Posted Tuesday, November 16, 2010 4:56 PM
 Ten Centuries Group: General Forum Members Last Login: Saturday, June 27, 2015 10:55 PM Points: 1,203, Visits: 376
 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 . 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));`
Post #1021866
 Posted Wednesday, November 17, 2010 9:15 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, October 22, 2012 6:30 AM Points: 7, Visits: 33
 Thanks very much. Wasn't aware of the hierarchy of types. Always good to end a day with something new between the ears. :)
Post #1022229
 Posted Friday, November 19, 2010 3:33 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, February 6, 2015 1:40 AM Points: 125, Visits: 30
 Excellent qustion. I was not aware of this. Got to learn something new... of course by choosing wrong answer
Post #1023383
 Posted Monday, March 30, 2015 1:39 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: 2 days ago @ 8:13 AM Points: 4,430, Visits: 479
 Good question, though I'm a little late to the party.
Post #1672873

 Permissions