|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
|
|
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.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Yesterday @ 6:48 AM
Points: 1,185,
Visits: 289
|
|
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));
|
|
|
|
|
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. :)
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:53 AM
Points: 123,
Visits: 28
|
|
Excellent qustion. I was not aware of this. Got to learn something new... of course by choosing wrong answer
|
|
|
|