Re: Error with implicit date calculation

On Feb 20, 11:29 pm, 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')
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Or the handy ANSI format for date literals:

DATE '1998-12-07' Received on Fri Feb 22 2008 - 11:44:14 CST