SQL Server - date comparison

Question

SELECT 
   CASE WHEN '04/10/2006' < '07/01/2013' THEN 'TRUE' ELSE 'FALSE'
END

Output: TRUE

SELECT
   CASE WHEN '10/31/2006' > '07/01/2012' THEN 'TRUE' ELSE 'FALSE' 
END

Output: TRUE

This is so confusing. I executed these two query and I got o/p as true for both the queries. The o/p to 1st query is correct. Shouldn't the output to 2nd query be false.? Can someone help please?

Answer 1

Those are strings without an explicit or implicit conversion to a date datatype.

The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn] - Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

Try this:

select 
   case  
   when convert(date,'20060410') < convert(date,'20130701') then 'true'
   else 'false'
   end

select 
   case  
   when convert(date,'20061031') > convert(date,'20120701') then 'true'
   else 'false'
   end
   

Answer 2

Always use standard date formats. Then you won't have a problem. I prefer YYYY-MM-DD, which will be converted to a correct date under almost all circumstances (if you leave out the hyphens, the conversion will always work):

 select  (case '2006-04-10' < '2013-07-01' then 'TRUE' else 'FALSE'
          end) as output_1
         (case  '2006-10-31 > '2012-07-01' then 'TRUE' else 'FALSE'
          end) as output_2

When you use the correct format, the comparisons will work as either strings or dates.