Re: Error with implicit date calculation

On Feb 23, 5:19 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "William Robertson" <williamr2..._at_googlemail.com> wrote in message
>
> news:73d6ee2e-eb65-4ffb-92e8-4179dccc127d_at_q78g2000hsh.googlegroups.com...
>
>
>
> > 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'
>
> I wouldn't do that. Use the to_date function with explicit specification
> for the format you are using. It makes it very clear what you are trying to
> do; in a way it documents how you want it converted. Much easier to
> maintain.
> Jim

What exactly isn't clear about ANSI date literals? They are unambiguous, compact and language-independent. I would strongly recommend using them. Received on Mon Feb 25 2008 - 02:26:02 CST