Re: Error with implicit date calculation

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 20 Feb 2008 15:29:49 -0800
Message-ID: <1203550164.855145@bubbleator.drizzle.com>


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')

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Feb 20 2008 - 17:29:49 CST

Original text of this message