Re: to_char put into a date?

In article <7mmhqt048d8aokbc3kiu0bvt9t965relh3_at_4ax.com>, Robert William Vesterman says...

You should use time_processed = sysdate instead of the to_char-conversion. At the moment you seem to be lucky, that the box you ran the population from has NLS_DATE set in a way that it understands 'MM/DD/YYYY HH24:MI:SS'. May be it's setting is 'DD/MM/YYYY HH24:MI:SS' so you will run into problems with some dates.

Don't know what the programmer thought about converting date to char to store it in a date field.

Matthias

>
>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 Thu Sep 20 2001 - 02:23:25 CDT