Oracle date format - strange behavior
I am writing a SQL query to retrieve data from a table between two dates. I give two inputs as shown. I am converting 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 am not getting any result for the first input. When I change it to the second one, I get the result. What is the difference between 01 / OCT / 2009 and 1 / OCT / 2009?
source to share
when comparing dates, you must always explicitly convert both sides of the statement to the DATE data type . Assuming StartDate and EndDate are DATE datatypes, this will 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 the VARCHAR collation. You are comparing strings and it is very unlikely that you will get the correct result (since months are NOT sorted alphabetically, for example).
If StartDate and EndDate are indeed VARCHAR, then you must explicitly convert them:
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 true for all versions of Oracle.
source to share
Since I don't have an Oracle client to validate my answer, it is undefined, but: In Oracle, one digit in the date string representing the day (D) is used to indicate the day of the week (Monday-Sunday), while 2 digits are used to specifying the day of the month (3 digits will represent the day of the year). Although you mentioned that you are converting the string using what seems to be the correct way of interpreting it, I think this might be the source of the problem (or at least it might be an explanation for the difference ..).
source to share