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 Thursday, November 4, 2010 11:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:00 AM
Points: 1,199, Visits: 335
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

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
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 9:52 PM
Points: 125, Visits: 29
Excellent qustion. I was not aware of this. Got to learn something new... of course by choosing wrong answer
Post #1023383
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse