to_char put into a date?

I recently tried to do some simple date arithmetic on a certain date field, something like:

... where time_processed <= sysdate - 1 ...

This resulted in an ORA-01843, "Not a valid month".

Investigation revealed that the root cause was that the program which populated the time_processed field didn't do so like this:

... set time_processed = sysdate ...

rather, it did it like this:

... set time_processed = to_char ( sysdate, 'MM/DD/YYYY HH24:MI:SS' )
...

I was surprised that no error results when the program populates time_processed this way, since time_processed is a date field, but it's being populated with a char.

Regardless, it does seem to work, and I discovered that I can do my arithmetic this way:

... where to_date ( time_processed, 'MM/DD/YYYY HH24:MI:SS' ) <=
sysdate - 1 ...

That seems to work fine. However, I am worried about it, as it just doesn't seem right.

Normally, I would not hesitate to fix the program that populates the field, so that it just sets it to sysdate rather than to_char ( sysdate, ... ), but the problem is that there are existing records out there using the old format. So, my new thing that's doing the arithmetic has to be able to handle that, and perhaps there are other existing things that rely upon it being in that format, too.

So, my question is, am I just lucky that things seem to be working the way they are, or is this existing method actually expected to work?

Thanks,

Bob Vesterman. Received on Wed Sep 19 2001 - 12:50:05 CDT