CODEWITHSUNDEEP
sqlsql-servert-sql
Orions Descendent
Orions Descendent

Reputation: 53

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?

Upvotes: 2

Views: 90

Answers (4)

David Cram
David Cram

Reputation: 778

It is doing a string compare rather than a date compare. This is dangerous when the value is actually a date. it is comparing the value of each ascii character. You can use a table like this one (http://www.ascii-code.com/) to see the values of ascii characters.
06/13/2016
06/14/2016
07/04/2014

Comapare 1 column at a time. The 0's are the same. 7 is greater than 6. So 07/04/2014 is your largest value. The first 2 dates are the same until you get to the 3/4 values. 3 is less than 4. So 06/13/2016 is less than 06/14/2016.

Since the first character with a higher or lower value determines whether the string is greater or less than, it doesn't work with dates unless you format all your dates to yyyy-mm-dd format. However, it is best to compare dates as dates rather than strings

Upvotes: 0

Serg
Serg

Reputation: 22811

Finally result of convert(cast(..)) is of varchar type. And constants in between are strings too. So Sql server has no choice but apply string comparison.

Upvotes: 0

ajeh
ajeh

Reputation: 2784

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.

Upvotes: 7

Rich Benner
Rich Benner

Reputation: 8113

Your issue is that you're comparing different data types. If you just compare dates then you won't get an issue;

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

It looks to me as you're basically treating these as text (varchar) it's going to do it character by character;

  1. is 0 between 0 and 0, yep
  2. is 6 between 6 and 7, yep
  3. is / between / and /, yep
  4. is 1 between 1 and 0, yep
  5. is 4 between 3 and 4, yep

etc etc

Upvotes: 1

Related Questions