Varchar2 column and Date Functions 2

[campbere]

I have a varchar2 column that has values int the following format 12/31/1998.
I am trying to search for rows based on this column as a date however one of two things happen:

1. If I use the following query a get an error that says not a valid month error at line 3

select product, extraction_date
from product_table
where id_number = '5639'
and extraction_date > to_date('12/31/1996', 'MM/DD/YYYY')

2. If I use the following query I don't get the rows I want

select product, extraction_date
from product_table
where id_number = '5639'
and extraction_date > '12/31/1996'

I get 12/31/1998.

Can someone help me? I am using Oracle 8.1.7

 

[sem]

Oracle converts column value to the data type compared with, so in the first case it tries to convert extraction_date to date by default (specified by NLS_DATE_FORMAT) format mask and raises error because of improper date format. You may either alter session to change NLS_DATE_FORMAT to 'mm/dd/yyyy' or type the condition as to_date(extraction_date,'mm/dd/yyyy') > to_date('12/31/1996', 'MM/DD/YYYY')

 

[karluk]

Right. Option 1 can be fixed with either of Sem's suggestions. Option 2 won't work. If you compare one varchar2 to another, '12/31/1996' will evaluate as greater than '01/01/1997' even though it is earlier as a date.

This may not be an option for you in this case, but in general it would probably be best to get in the habit of using the date datatype to store dates. Then this type of awkward situation won't arise.

 

[ajaykumar21]

HI,

I am Ajay from India. You can use this query like this. This will surely give you results.

select product, extraction_date
from product_table
where id_number = '5639'
and TO_DATE(extraction_date,'MM/DD/YYYY') > to_date('12/31/1996', 'MM/DD/YYYY')