Datetime Overflow Error

priyankalaisatwar · Post by **priyankalaisatwar** » Fri Aug 30, 2013 12:19 am

I am trying to Load data From DB2 to SQL Server, I am getting datetime overflow error. I tried to just load timestamp without microseconds but still getting the same error. Please help! The job is in production and I am getting the error.

SURA · Post by **SURA** » Fri Aug 30, 2013 12:26 am

Let us know what you have tried from your end?

priyankalaisatwar · Post by **priyankalaisatwar** » Fri Aug 30, 2013 12:53 am

Hi Sura,

First I took one stage variable STG1 and dump the Timestamp column into it. like below
left(timestamp_column,19) --> stg1
and the in transformer output link, In the output Timestamp column I did:-

StringToTimestamp(STG1,"%yyyy-%mm-%dd %hh:%nn:%ss.0") --> Output_timestamp_column

Getting the error :-
[IBM(DataDirect OEM)][ODBC SQL Server Driver]Datetime field overflow [odbcUtils.C:1352]"
Please suggest

umaisnazir · Post by **umaisnazir** » Fri Aug 30, 2013 2:31 am

StringToTimestamp(STG1,"%yyyy-%mm-%dd %hh:%nn:%ss.0") -->
StringToTimestamp(STG1,"%yyyy-%mm-%dd %hh:%nn:%ss") Remove the 0 and try .

priyankalaisatwar · Post by **priyankalaisatwar** » Fri Aug 30, 2013 5:10 am

After Removing .0 also I am getting the same error. Do I need to change the lenght of this column in Transformer Output Stream?

priyadarshikunal · Post by **priyadarshikunal** » Fri Aug 30, 2013 5:16 am

what is the column type in SQL server for this column?

umaisnazir · Post by **umaisnazir** » Fri Aug 30, 2013 5:18 am

Can you please check the data type of your target column?
Also, timestamp of 38 should accept it .

priyankalaisatwar · Post by **priyankalaisatwar** » Fri Aug 30, 2013 5:41 am

Column Type in Target SQL server is Datetime and SOURCe Datatype is DB2 - Timestamp

ArndW · Post by **ArndW** » Tue Sep 03, 2013 2:33 am

What is the exact DDL definition of your SQL-Server datetime column?
The DB2 timestamp type has 9 microsecond digits, and not even the SQL-Server datetime2(7) can store that much, and you are using datetime which barely has 3 centisecends.

SURA · Post by **SURA** » Tue Sep 03, 2013 2:52 am

priyankalaisatwar wrote:Column Type in Target SQL server is Datetime and SOURCe Datatype is DB2 - Timestamp

I am not in the DS. Can you please share you date like format from source as well as target!

All you need to do is a proper conversion before load the data into SQL Server and you have functions in TFM & Modify stage.

priyankalaisatwar · Post by **priyankalaisatwar** » Tue Sep 03, 2013 6:15 am

I tried to load that column as varchar, Now data is getting loaded and I am not getting any warnings. But I want to load it in datetime dattype only.

chulett · Post by **chulett** » Tue Sep 03, 2013 7:07 am

Answer Arnd's question. Post examples of your extracted DB2 data.

DSXchange

Datetime Overflow Error

Datetime Overflow Error

Re: Datetime Overflow Error

Re: Datetime Overflow Error

Re: Datetime Overflow Error

Re: Datetime Overflow Error

Re: Datetime Overflow Error