Orions Descendent Orions Descendent - 5 months ago 10
SQL Question

I would like to understand how the following string is between 2 other strings

Note this is my first post :)

I was working on a program and I saw some code that behaved unexpectedly. I know there is some implicit data conversions but I do not understand how it evaluates to true...

I ran the below code and it pulled 'Yes' instead of 'No' as I expected.

The code used GETDATE(). I changed it to cast today's date so the code can be ran on another day if needed just for this post.

SELECT CASE
WHEN CONVERT(VARCHAR,CAST('6/14/2016' AS DATETIME),101) BETWEEN '06/13/2014' AND '07/04/2014'
THEN 'Yes'
ELSE 'NO'
END


Can someone explain to me the how it evaluates to true?

Answer

You are comparing the strings as your date is converted to VARCHAR. And I bet the strings are sorted alphabetically. Let's test this theory:

declare @t table (a varchar(10))
insert into @t select '06/14/2016'
insert into @t select '06/13/2014'
insert into @t select '07/04/2014'

select a from @t order by a

This will output:

a
----------
06/13/2014
06/14/2016
07/04/2014

(3 row(s) affected)

Your 2016 date is the 2nd record, so it will fall between the other dates. If you want to treat those as dates, do not convert to VARCHAR. Then the other two dates will be converted to date type implicitly:

SELECT CASE WHEN CAST('6/14/2016' AS DATETIME) BETWEEN '06/13/2014' AND '07/04/2014' THEN 'Yes' ELSE 'NO' END

will output NO that you are expecting.

PS: To avoid unpredictable results, always declare/convert to VARCHAR(n) as VARCHAR conversion without specifying size uses the first string it encounters to determine size of the column in the result set and the rest may be truncated.