Page 1 of 2
Datatime Field Overflow
Posted: Fri Jan 05, 2007 11:33 am
by iwin
Hi,
I am trying to load a Sql Server table sourced from Oracle table with Effect start date and end date as of two date fields. After loading 9k records i am getting an Error:
" SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow"
Any idea abt why i am getting this error?
thanks in advance.
Posted: Fri Jan 05, 2007 11:42 am
by DSguru2B
Search this site, its been covered gazillions of times.
Posted: Fri Jan 05, 2007 11:45 am
by I_Server_Whale
SQL Server expects the date to be in the following format:
where, TTT is the milliseconds part.
In what format are you getting the date from Oracle? How is the metadata defined for these fields on source and target side?
Whale.
Posted: Fri Jan 05, 2007 11:53 am
by iwin
Hi,
I am getting the date from oracle in "yyyy-mm-dd hh:mm:ss" format with column generated SQL and defind as timestamp datatype. Coming to Target Sql server it is also defined as timestamp datatype in Datastage. I am getting this error after 9K records are loaded. I viewed the data and the datetime format in target is "YYYY-MM-DD HH:MM:SS.TTT".
Thanks.
I_Server_Whale wrote:SQL Server expects the date to be in the following format:
where, TTT is the milliseconds part.
In what format are you getting the date from Oracle? How is the metadata defined for these fields on source and target side?
Whale.
Posted: Fri Jan 05, 2007 11:58 am
by I_Server_Whale
Can you post the information after this error message:
Code: Select all
" SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow"
I mean, the data for which(dates) it fails.
Whale.
Posted: Fri Jan 05, 2007 12:00 pm
by iwin
I dont see any information after this message.
I_Server_Whale wrote:Can you post the information after this error message:
Code: Select all
" SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow"
I mean, the data for which(dates) it fails.
Whale.
Posted: Fri Jan 05, 2007 12:03 pm
by Krazykoolrohit
are there any spaces or blanks in dae field? check for them in oracle table by firing a query.
Posted: Fri Jan 05, 2007 12:08 pm
by iwin
Thanks rohit,
I did check it. There are no spaces or nulls in date field. And also i dont get the row which is generating this error
Krazykoolrohit wrote:are there any spaces or blanks in dae field? check for them in oracle table by firing a query.
Posted: Fri Jan 05, 2007 12:17 pm
by I_Server_Whale
iwin wrote:Hi,
I am getting the date from oracle in "yyyy-mm-dd hh:mm:ss" format with column generated SQL and defind as timestamp datatype. Coming to Target Sql server it is also defined as timestamp datatype in Datastage. I am getting this error after 9K records are loaded. I viewed the data and the datetime format in target is "YYYY-MM-DD HH:MM:SS.TTT".
What are the lengths and scales of these date fields defined on the source and target side?
Whale.
Posted: Fri Jan 05, 2007 12:30 pm
by iwin
Hi ,
Source Oracle:
Datatype :Timestamp
Length : 38
Scale:0
Target Sql Server:
Datatype : Timestamp
Length: 23
Scale :3.
Thanks.
I_Server_Whale wrote:iwin wrote:Hi,
I am getting the date from oracle in "yyyy-mm-dd hh:mm:ss" format with column generated SQL and defind as timestamp datatype. Coming to Target Sql server it is also defined as timestamp datatype in Datastage. I am getting this error after 9K records are loaded. I viewed the data and the datetime format in target is "YYYY-MM-DD HH:MM:SS.TTT".
What are the lengths and scales of these date fields defined on the source and target side?
Whale.
Posted: Fri Jan 05, 2007 12:45 pm
by I_Server_Whale
Let's test it out in a different way. Can you load the data into sequential file instead of your target db? Also, put a constraint in there as
Now see what the dates are like in the sequential file? Post them if they look suspicious.
Whale.
Posted: Fri Jan 05, 2007 1:01 pm
by kcbland
Are these the only date columns? Profile the source date columns and make sure there are no NULLs or invalid dates.
Posted: Fri Jan 05, 2007 1:56 pm
by iwin
Hi Ken,
Yes, these are only date columns defined as not null in source oracle table.
Thanks
kcbland wrote:Are these the only date columns? Profile the source date columns and make sure there are no NULLs or invalid dates.
Posted: Fri Jan 05, 2007 2:38 pm
by kcbland
Do a select min(yourcolumnA), max(yourcolumnA), min(yourcolumnB), max(yourcolumnb) from yoursourcetable and see if the results will work within SQL Server. You may have Oracle OK dates but not SQL-Server OK dates.
Posted: Fri Jan 05, 2007 2:57 pm
by iwin
Hey Ken,
I just noticed that the min date value in oracle is "0016/11/10" where as for Sql server it is "1990/01/03". I am assuming this maight be the problem.
Any inputs please.
Thanks
kcbland wrote:Do a select min(yourcolumnA), max(yourcolumnA), min(yourcolumnB), max(yourcolumnb) from yoursourcetable and see if the results will work within SQL Server. You may have Oracle OK dates but not SQL-Server OK dates.