Datatime Field Overflow
Moderators: chulett, rschirm, roy
Datatime Field Overflow
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.
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.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
Code: Select all
YYYY-MM-DD HH:MM:SS.TTT
In what format are you getting the date from Oracle? How is the metadata defined for these fields on source and target side?
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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 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.Code: Select all
YYYY-MM-DD HH:MM:SS.TTT
In what format are you getting the date from Oracle? How is the metadata defined for these fields on source and target side?
Whale.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Can you post the information after this error message:
I mean, the data for which(dates) it fails.
Whale.
Code: Select all
" SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow"
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
I dont see any information after this message.
I_Server_Whale wrote:Can you post the information after this error message:
I mean, the data for which(dates) it fails.Code: Select all
" SQLSTATE=22008, DBMS.CODE=0 [DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow"
Whale.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
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
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.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
What are the lengths and scales of these date fields defined on the source and target side?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".
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Hi ,
Source Oracle:
Datatype :Timestamp
Length : 38
Scale:0
Target Sql Server:
Datatype : Timestamp
Length: 23
Scale :3.
Thanks.
Source Oracle:
Datatype :Timestamp
Length : 38
Scale:0
Target Sql Server:
Datatype : Timestamp
Length: 23
Scale :3.
Thanks.
I_Server_Whale wrote:What are the lengths and scales of these date fields defined on the source and target side?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".
Whale.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
Code: Select all
@INROWNUM > 9000
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Are these the only date columns? Profile the source date columns and make sure there are no NULLs or invalid dates.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Hi Ken,
Yes, these are only date columns defined as not null in source oracle table.
Thanks
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.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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.
Every person you meet knows something you don't, Learn from them.
-- H. Jackson Brown
-- H. Jackson Brown