 | |
Error with implicit date calculation
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Error with implicit date calculation -
02-22-2008
, 11:44 AM
|
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...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org |
DATE '1998-12-07'
#22
| |||
| |||
|
|
On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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' |
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
#23
| |||
| |||
|
|
On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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' |
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
#24
| |||
| |||
|
|
On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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' |
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
#25
| |||
| |||
|
|
On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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' |
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
#26
| |||
| |||
|
|
"William Robertson" <williamr2... (AT) googlemail (DOT) com> wrote in message news:73d6ee2e-eb65-4ffb-92e8-4179dccc127d (AT) q78g2000hsh (DOT) googlegroups.com... On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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 |
unambiguous, compact and language-independent. I would strongly
recommend using them.
#27
| |||
| |||
|
|
"William Robertson" <williamr2... (AT) googlemail (DOT) com> wrote in message news:73d6ee2e-eb65-4ffb-92e8-4179dccc127d (AT) q78g2000hsh (DOT) googlegroups.com... On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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 |
unambiguous, compact and language-independent. I would strongly
recommend using them.
#28
| |||
| |||
|
|
"William Robertson" <williamr2... (AT) googlemail (DOT) com> wrote in message news:73d6ee2e-eb65-4ffb-92e8-4179dccc127d (AT) q78g2000hsh (DOT) googlegroups.com... On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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 |
unambiguous, compact and language-independent. I would strongly
recommend using them.
#29
| |||
| |||
|
|
"William Robertson" <williamr2... (AT) googlemail (DOT) com> wrote in message news:73d6ee2e-eb65-4ffb-92e8-4179dccc127d (AT) q78g2000hsh (DOT) googlegroups.com... On Feb 20, 11:29 pm, 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') -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@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 |
unambiguous, compact and language-independent. I would strongly
recommend using them.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.