Hello, I'm on the reporting team at an institution in Florida and we are having an interesting SQL problem.
Take a look at these two queries,
SELECT DISTINCT a.sdb_3026_crse_enrl,
TO_DATE(a.sdb_3026_crse_enrl, 'MMddyyyy')
FROM ps_fsc_rp_sdbrt6hs a
WHERE a.strm = '2185' AND
a.fsc_submission_typ = 'E' AND
a.sdb_3026_crse_enrl <> '99999999';
The field sdb_3026_crse_enrl is varchar and holds dates encoded like "mmddyyyy" and if the date is unknown or not reported, the value "99999999" is used. As you can see, I'm filtering these non-dates out. Therefore, the select clause is able to convert all the remaining values into dates.
However, if I do this
SELECT DISTINCT a.sdb_3026_crse_enrl,
TO_DATE(a.sdb_3026_crse_enrl, 'MMddyyyy')
FROM ps_fsc_rp_sdbrt6hs a
WHERE a.strm = '2185' AND
a.fsc_submission_typ = 'E' AND
a.sdb_3026_crse_enrl <> '99999999' AND
TO_DATE(a.sdb_3026_crse_enrl, 'MMddyyyy') < SYSDATE;
I get an invalid month error. It's almost as if it's trying to convert "99999999" values anyway in the select clause. If anyone knows anything about how predicates are evaluated in Oracle SQL that would be pertinent to this issue or how to coerce Oracle to be able to do this, please let me know. Here is my oracle version: "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production"