CODEWITHSUNDEEP
sqlsql-server
Rohit Sharma
Rohit Sharma

Reputation: 379

Date comparison in varchar format

IF('08/30/2015'>'08/29/2016')
SELECT '1'
ELSE
SELECT '0'

This SQL statement returns 1 because it is comparing only month and day and ignoring the year. I know I am comparing two varchar values - not the dates - but I want to know what logic SQL follows for this comparison?

Can anybody help me please? This same scenario gave me an issue in a project, and I fixed that by casting to date. However, I want to know the logic behind this. I searched it but didn't find a proper explanation.

Upvotes: 0

Views: 1528

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your values are not dates. They are strings. You could do:

IF (convert(date, '08/30/2015') > convert(date, '08/29/2016'))
    SELECT '1'
ELSE
    SELECT '0';

(Note: Whether or not this works may depend on your internationalization settings for the date format. I assume that is set up to understand MDY format.)

But why bother? Just use ISO standard date formats (YYYY-MM-DD):

IF ('2015-08-30' > '2015-08-29')
    SELECT '1'
ELSE
    SELECT '0';

These don't need a conversion because the strings compare correctly.

Upvotes: 4

morgb
morgb

Reputation: 2312

It's comparing two character strings, so even though a human would look at these and think of dates, SQL just sees that one character string is greater than another. If you take out the slashes and just put the numbers out there, it might make more sense: 08302015 > 08292016.

Upvotes: 0

Related Questions