Error with implicit date calculation

#1

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.

#2

Arch wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

Arch wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

Arch wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

Arch wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

#7

On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

#8

On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

#9

On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

Quote:

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.

#10

On Feb 21, 12:14*am, Arch <send... (AT) spam (DOT) net> wrote:

Quote:

On Wed, 20 Feb 2008 15:29:49 -0800, DA Morgan <damor... (AT) psoug (DOT) 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 --