Re: Error with implicit date calculation

On Thu, 21 Feb 2008 13:12:14 -0800 (PST), Mark D Powell <Mark.Powell_at_eds.com> wrote:

>On Feb 21, 12:14 am, Arch <send..._at_spam.net> wrote:
>> On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damor..._at_psoug.org>
>> wrote:
>>
>>
>>
>>
>>
>> >Arch wrote:
>> >> I need a hand with SQL. I'm using 9.2.0.4.
>>
>> >> I need to compute a date offset by a number of weeks from another
>> >> date.  This is a line from a view that I had been using:
>>
>> >> TO_DATE('7-Dec-1998') + (WeekNum * 7) As WeekDate
>>
>> >> WeekNum is, of course, the field that contains the number of weeks to
>> >> offset from the date.  This used to work just fine - it doesn't any
>> >> longer.  The TO_DATE function now returns the error:
>>
>> >>      ORA_1858 a non-numeric character was found where a
>> >>           numeric character was expected
>>
>> >> If I change it to TO_DATE('7-Dec-2000') or any later date, it no
>> >> longer gives an error.  It does not return the desired value, of
>> >> course, but no error.  Any date 1999 or earlier causes the error.
>>
>> >> I would appreciate any advice how to correct this.
>>
>> >It would likely help if you used the full syntax:
>>
>> >TO_DATE('07-DEC-1998', 'DD-MON-RRRR')
>> >or
>> >TO_DATE('07-DEC-1998', 'DD-MON-YYYY')
>>
>> Thank you.- Hide quoted text -
>>
>> - Show quoted text -
>
>Yes, I was going to ask what the nls_date_format was set to since the
>default is for a 2 digit year. I have found it is always better to
>write code that uses the to_date function with a format mask specific
>to the application logic. That way when someone changes the session
>or worse system parameters your code is much more likely to continue
>to function correctly.
>
>HTH -- Mark D Powell --
>

Thanks, Mark. That's a mistake that I won't repeat. Received on Fri Feb 22 2008 - 06:29:44 CST