SQL & PL/SQL

Hi everyone,

I am using FND_DATE.canonical_to_date to convert character columns to a specific date format and of course, in a date datatype.

ex:

SELECT COL1
FROM TABLEA;

COL1 ---->VARCHAR2 datatype
-------
2007/09/20 00:00:00

is easily converted to date using the following SQL:

SELECT FND_DATE.CANONICAL_TO_DATE(COL1)
FROM TABLEA;

FND_DATE.CANONICAL_TO_DATE(COL1) ----> DATE datatype
------------------------------------------------------
2007/09/20

Retrieving the data using the second SQL gave me no error/problem. However when I try to use it in the WHERE clause, I get some errors in the FND_DATE package. The error says that the COL1 data input was either short or invalid but when I simply retrieve it, no error was displayed.

SELECT FND_DATE.CANONICAL_TO_DATE(COL1)
FROM TABLEA; ----------------------> NO ERROR

SELECT FND_DATE.CANONICAL_TO_DATE(COL1)
FROM TABLEA
WHERE FND_DATE.CANONICAL_TO_DATE(COL1) IS NULL; ---------->ERROR!

How do I solve this kind of problem? I can't go back to TO_DATE and I can't use "COL1 IS NULL" at the where clause because I am using a VIEW then selecting data from that VIEW and adding some conditions.

CREATE OR REPLACE VIEW AAA
(A_COL1)
AS
SELECT FND_DATE.CANONICAL_TO_DATE(COL1)
FROM TABLEA
/

then when I try to query it like this:

SELECT *
FROM AAA
WHERE A_COL1 IS NULL; --------------> ERROR!

The actual SQL statement is more complicated than this.

Any kind of help is truly appreciated.

Thanks!

NG