Page 1 of 1
Datetime Overflow Error
Posted: Fri Aug 30, 2013 12:19 am
by priyankalaisatwar
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.
Re: Datetime Overflow Error
Posted: Fri Aug 30, 2013 12:26 am
by SURA
Let us know what you have tried from your end?
Re: Datetime Overflow Error
Posted: Fri Aug 30, 2013 12:53 am
by priyankalaisatwar
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
Re: Datetime Overflow Error
Posted: Fri Aug 30, 2013 2:31 am
by umaisnazir
StringToTimestamp(STG1,"%yyyy-%mm-%dd %hh:%nn:%ss.0") -->
StringToTimestamp(STG1,"%yyyy-%mm-%dd %hh:%nn:%ss") Remove the 0 and try .
Re: Datetime Overflow Error
Posted: Fri Aug 30, 2013 5:10 am
by priyankalaisatwar
After Removing .0 also I am getting the same error. Do I need to change the lenght of this column in Transformer Output Stream?
Posted: Fri Aug 30, 2013 5:16 am
by priyadarshikunal
what is the column type in SQL server for this column?
Re: Datetime Overflow Error
Posted: Fri Aug 30, 2013 5:18 am
by umaisnazir
Can you please check the data type of your target column?
Also, timestamp of 38 should accept it .
Posted: Fri Aug 30, 2013 5:41 am
by priyankalaisatwar
Column Type in Target SQL server is Datetime and SOURCe Datatype is DB2 - Timestamp
Posted: Tue Sep 03, 2013 2:33 am
by ArndW
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.
Posted: Tue Sep 03, 2013 2:52 am
by SURA
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.
Posted: Tue Sep 03, 2013 6:15 am
by priyankalaisatwar
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.
Posted: Tue Sep 03, 2013 7:07 am
by chulett
Answer Arnd's question. Post examples of your extracted DB2 data.