The string representation of a datetime value is out of rang

prmuttireddy · Post by **prmuttireddy** » Wed Oct 03, 2007 6:10 am

Hi,

While i am running the job I am getting following warnings.

TRNAddSubRecVal: [IBM][CLI Driver][DB2] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007.

Please provide how to resolve the above one.

Advanced thanks.

Regards,
PReddy.

chulett · Post by **chulett** » Wed Oct 03, 2007 6:43 am

Please provide more information. Details about your job design, your data, what you are sending to the field generating the error, etc etc.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 03, 2007 1:57 pm

Learn what the highest date that can legally be represented in your DB2 instance is, and check that no later date is included in your data. Be wary of the format of the date also, for example don't supply mmddyyyy if yyyy-mm-dd is expected.

prmuttireddy · Post by **prmuttireddy** » Thu Oct 04, 2007 4:08 am

Hi,

Actually The scenario is

DRS stage-->Transformer-> DRS

I am changing filed date(10) to Timestamp(10) in the Transformer stage.

Regards,
Reddy.

Maveric · Post by **Maveric** » Thu Oct 04, 2007 4:16 am

Change the timestamp field length from 10 to 18 and try.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 04, 2007 4:23 pm

What does this number represent for the Timestamp data type? Is it the number of characters, or the number of fractional second digits?

jdmiceli · Post by **jdmiceli** » Thu Oct 04, 2007 4:29 pm

If you change the field length, I think you also have to change the Display length. DB2 defaults the format of timestamps to YYYY-MM-DD HH:MM:SS.mmmmmm (I think). If the process is converting that to char or varchar, then you need more than 18 or you will truncate.

Don't know if that helps or not!

Bestest,

prmuttireddy · Post by **prmuttireddy** » Fri Oct 05, 2007 1:36 am

Thanks to all.
Hi Ray,

The Timestamp datatype represents the number of characters.

Actully data type that field in the database is DATE(10).

Regards,
Reddy.