Oracle Date format - Strange behaviour

Question

I am writing a SQL query to retrive data from a table between two dates. I give two inputs as shown. I convert the Date TO_CHAR(DD/MON/YYYY).

 1. StartDate > 01/SEP/2009 
    EndDate < 01/OCT/2009

 2. StartDate > 01/SEP/2009
    EndDate < 1/OCT/2009

I dont get any result for the first input. When I change it to second one i get the result. What is the difference between 01/OCT/2009 1/OCT/2009

Answer 1

Since I don't have an Oracle client available to verify my answer, it's not certain but: In Oracle, a single digit in the date string representing the day (D) is used to specify the day of week (Monday-Sunday), while 2 digits are used to specify the day of month (3 digits will represent the day in the year). Although you mentioned you're converting the string using what seems like the right way to do the interpretation, I think this could be the source of the problem (or at least could be an explanation to the difference..).

Answer 2

Hi Orthus,

when comparing dates you should always convert explicitely both sides of the operator to DATE datatype. Assuming StartDate and EndDate are both DATE datatype, this would work:

 StartDate > to_date('01/SEP/2009', 'dd/MON/yyyy')
 AND EndDate < to_date('01/OCT/2009', 'dd/MON/yyyy')

In your case however the result you get is consistent with VARCHAR comparison. You're comparing strings and it's VERY unlikely you will get the correct result (since months are NOT sorted alphabetically for instance).

If StartDate and EndDate are indeed VARCHAR you should convert them explicitely:

 to_date(StartDate, 'dd/MON/yyyy') > to_date('01/SEP/2009', 'dd/MON/yyyy')
 AND to_date(EndDate, 'dd/MON/yyyy') < to_date('01/OCT/2009', 'dd/MON/yyyy')

This is valid for all versions of Oracle.