SQLplus Inserted Value Too Large in DATE field

[zoroaster]

Hi All

I'm very new to SQLplus, so forgive me if I'm blundering around in the dark here, but I wanted to do a simple one-off update on some of the records on a date field, which for some reason have been uploaded wrongly and ended up 100 years out - all the year values are 1903 instead of 2003. However the SQL I'm using results in a 'ORA-01401: inserted value too large for column' error, which is confusing me - surely a date field is 8 bytes regardless? Here's the SQL I'm trying to run:

update prevemp set end_d=add_months(to_date(end_d,'YYYYMMDD'),1200) where end_d NOT like ' %' and end_d<'19031231';

Any ideas what I'm doing wrong/how I can get round the problem? Is there any way in SQLplus to return the data-type of the column I'm reporting on, just to check? Many thanks in advance

Laters, Z

"42??? We're going to get lynched!

 

[sem]

Is end_d of DATE data type? If so, you need not convert it TO_DATE. If not, it would be better to explicitly convert the result of add_months according to the required ( 'YYYYMMDD' ?) format mask. Otherwise you depend on the NLS_DATE_FORMAT value, that in turn may be also changed using ALTER SESSION command.

Regards, Dima

http://seminihin.narod.ru

 

[zoroaster]

Thanks Dima

As I was beginning to suspect and you explicitly asked, the 'end-d' field is stored as 8 characters, and not a date. I've just downloaded Oracle's SQL*plus reference and found the DUMP function, which allowed me to confirm this - so I've corrected the SQL to:

select end_d, to_char(add_months(to_date(end_d,'YYYYMMDD'),1200),'YYYYMMDD') as resultant from prevemp where end_d<'19031231' and end_d NOT like ' %';

which works just fine. I'm sure there are other ways of doing it (i.e. changing the first 2 chars to '20' etc) but this has done what I want. I think the date must be held that way because the database software has to function on more than just the Oracle platform.

Many thanks for nudging me in the right direction!

Laters, Z

"42??? We're going to get lynched!