SQL & PL/SQL

Hello All,

I'm having an issue using the to_date function that has me quite perplexed.

I have two varchar2 fields, one with a date value in the format Mon, DD YYYY, the other has a time value in the format HH:MI PM.

When I run my query one of the columns I retrieve looks like this TO_DATE (d4.adate || e4.atime, 'Mon DD, YYYYHH:MI PM'). The two fields are concatenated together and converted to a date. This works fine.

My problem occurs when I attempt to apply the same logic to the where clause of the aforementioned query. e.g. when I add the following criteria to my query and TO_DATE (d4.adate || e4.atime, 'Mon DD, YYYYHH:MI PM') <= sysdate I get an ORA-01843: not a valid month error.

To further illustrate my problem here are the two queries:

Select d4.adate, e4.atime, TO_DATE (d4.adate || e4.atime, 'Mon DD, YYYYHH:MI PM')
from ....
where ....

The above query works.

Select d4.adate, e4.atime, TO_DATE (d4.adate || e4.atime, 'Mon DD, YYYYHH:MI PM')
from ....
where ....
and TO_DATE (d4.adate || e4.atime, 'Mon DD, YYYYHH:MI PM') <= sysdate

The second query does not work.

The tables used and the limiting criteria are identical, except for the last one.

Does anyone have any ideas why this could be happening.

er