Re: Searching on a date

You are comparing apples and oranges (or dates and strings to be more precise). You have to convert data types so your comparison will work.

> WHERE (CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE)
> <> TO_DATE('29/DEC/1899')

Out of your WHERE clauses, this is the closest to being right.

Try changing the TO_DATE() call to this:

TO_DATE('29/DEC/1899', 'DD/MMM/YYYY') That way Oracle knows the middle element is a three-character month name abbreviation. Look at the documentation of TO_DATE() to find out all the possible formats you can use.

Also, you should be aware that in Oracle date fields also hold the time, so if the date has a time component, they are not considered equal:

TO_DATE('29/DEC/1899', 'DD/MMM/YYYY') does _not_ equal this:

TO_DATE('29/DEC/1899 13:00:00', 'DD/MMM/YYYY HH24:MI:SS') To match the dates only (while ignoring their time components) you have to use the TRUNC() function like this:

TRUNC(TO_DATE('29/DEC/1899', 'DD/MMM/YYYY')) _does_ equal this:

TRUNC(TO_DATE('29/DEC/1899 13:00:00', 'DD/MMM/YYYY HH24:MI:SS')) There are other ways of doing this sort of comparison, but this is a place to start. Received on Wed Feb 28 2001 - 00:18:51 CST