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.
WHEN CONVERT(VARCHAR,CAST('6/14/2016' AS DATETIME),101) BETWEEN '06/13/2014' AND '07/04/2014'
You are comparing the strings as your date is converted to
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 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.