Datatime Field Overflow

iwin · Post by **iwin** » Fri Jan 05, 2007 11:33 am

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.

DSguru2B · Post by **DSguru2B** » Fri Jan 05, 2007 11:42 am

Search this site, its been covered gazillions of times.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 05, 2007 11:45 am

SQL Server expects the date to be in the following format:

Code: Select all


YYYY-MM-DD HH:MM:SS.TTT

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.

iwin · Post by **iwin** » Fri Jan 05, 2007 11:53 am

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:
Code: Select all
YYYY-MM-DD HH:MM:SS.TTT
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.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 05, 2007 11:58 am

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.

iwin · Post by **iwin** » Fri Jan 05, 2007 12:00 pm

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.

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Jan 05, 2007 12:03 pm

are there any spaces or blanks in dae field? check for them in oracle table by firing a query.

iwin · Post by **iwin** » Fri Jan 05, 2007 12:08 pm

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.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 05, 2007 12:17 pm

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.

iwin · Post by **iwin** » Fri Jan 05, 2007 12:30 pm

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.

I_Server_Whale · Post by **I_Server_Whale** » Fri Jan 05, 2007 12:45 pm

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

Code: Select all


@INROWNUM > 9000

Now see what the dates are like in the sequential file? Post them if they look suspicious.

Whale.

kcbland · Post by **kcbland** » Fri Jan 05, 2007 1:01 pm

Are these the only date columns? Profile the source date columns and make sure there are no NULLs or invalid dates.

iwin · Post by **iwin** » Fri Jan 05, 2007 1:56 pm

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.

kcbland · Post by **kcbland** » Fri Jan 05, 2007 2:38 pm

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.

iwin · Post by **iwin** » Fri Jan 05, 2007 2:57 pm

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.