Can someone help me in understanding the output of this TSQL script

declare @.startdate datetime

declare @.enddate datetime

declare @.testvalue datetime

set @.startdate = '2005-12-31'

set @.enddate = '2006-12-29'

set @.testvalue ='2006-05-17'

if convert(varchar(20), @.testvalue, 102) between convert(varchar(20), @.startdate, 102) and convert(varchar(20), @.enddate, 102)

print 'yes'

else

print 'no'

-

The above script print yes with format specifier as 102 where as it prints no with format specifier as 102. Why/how does the format specifier affect the output?

I agree that there are better was of achieving what is done in the above script. But I am curious to know the why sql server behaves this way in the above query.

Thanks

Take a look at the output of the following SQL:

declare @.startdate datetime
declare @.enddate datetime
declare @.testvalue datetime
set @.startdate = '2005-12-31'
set @.enddate = '2006-12-29'
set @.testvalue ='2006-05-17'

SELECT Mode = 'No Format', TestVal = convert(varchar(20), @.testvalue),
StartVal = convert(varchar(20), @.startdate),
EndVal = convert(varchar(20), @.enddate)
UNION ALL
SELECT 'Format 102', convert(varchar(20), @.testvalue, 102),
convert(varchar(20), @.startdate, 102),
convert(varchar(20), @.enddate, 102)

This gives the following results:

No Format | May 17 2006 12:00AM | Dec 31 2005 12:00AM |Dec 29 2006 12:00AM
Format 102 | 2006.05.17 | 2005.12.31 | 2006.12.29

As you can see the no format provides date text strings which do not sort alphabetically in date order at all. Format 102 produces a text string which does sort in date order when sorted alphabetically so can be used in the comparison. Of course the simple way to do it is to perform the comparison directly on the date data.

|||

I agree with your explanation for format specifier 102. However, This means that my script in the first thread should work with the format specifier as 101. But it doesnt. Can you explain this behaviour please?

|||

If you are comparing date values, you 'should NOT' be converting to varchar!

It is wasted effort and slows your process down.

|||

That's the reason i mentioned in my first post that we can do this in a better way. I am of the opinion that we have to use datetime instead of varchar(20).

But what i want to know is why does the change in format specifier (in my query) change the output value.

With 101 format specifier the expression evaluates to false where as with 102 it evaluates to true. Need to know/understand this behaviour of SQL server wrt the format specifier change.

|||

For exactly the same reason - again this is a text-based comparison.

Here are the strings you are comparing, ordered alphanumerically ascending:

--102
2005.12.31 - @.startdate
2006.05.17 - @.testvalue
2006.12.29 - @.enddate

--101
05/17/2006 - @.testvalue
12/29/2006 - @.enddate
12/31/2005 - @.startdate

It's clear to see that in the second example, @.testvalue < @.enddate. In fact using format 101 makes your BETWEEN condition impossible to meet.

Chris

|||Thanks Chris!