Re: Error with implicit date calculation
From: news.verizon.net <kennedyii_at_verizon.net>
Date: Sat, 23 Feb 2008 05:19:39 GMT
Message-ID: <L1Ovj.1125$Hd.198@trnddc02>
>> 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
Date: Sat, 23 Feb 2008 05:19:39 GMT
Message-ID: <L1Ovj.1125$Hd.198@trnddc02>
"William Robertson" <williamr2019_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
Received on Fri Feb 22 2008 - 23:19:39 CST