Jostein Saethern (11/27/2011)
The question is not really about datetime precision, but string comparison.If the comparison value had been declared as datetime, then the other values would be implicitly converted to datetime as well and the statement would return Yes.
declare @d as datetime
set @d = '2011-07-31 00:00:00.000'
IF @d BETWEEN '2011-07-01' and '2011-07-31'
PRINT 'Yes'
ELSE
PRINT 'No'
The question as it stands could just as well have been this
if ('abcd' between 'ab' and 'abc')
print 'yes'
else
print 'no'
You are right, I'ts very obvious when you think about it, but when you see something that looks like a date, you assume that SQL server see it as a date. BUT it would be dangerous if SQL server would interpret the datatypes based on the values, and this is just another reason to dislike the implicit conversions. (I know, there is no implicit conversion in the question, but we have learned to live with them so long that we assume it takes place all the time).
I really would like a database setting, a trace flag or something that warn you or prevent you from using implicit conversion and force you to use explicit conversion. Because if you do, you know that you are doing something wrong (from a performance perspective).
The explanation is not correct and should be changed.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform