I am trying to insert records in a SQL Server table with date fields that are datatype "date". Somehow the date fields seem to default to "smalldatetime", which prevents me from inserting dates outside of the range between 01/01/1900 through 06/06/2079. I need to insert records with years up to 9999 and as low as 1753 (please don't ask why... this is a user request).
This is a simple job that reads from a flat file, goes through a simple date validation and is written to the SQL Server table.
Below is an example of what I coded in the Transform stage:
l_inFFSCur.CRTE_DT[1,4] : "-" : l_inFFSCur.CRTE_DT[5,2] : "-" : l_inFFSCur.CRTE_DT[7,2]
This is the error I get when I try to insert year 17530101 or 99990101 in the date field:
APT_CombinedOperatorController,0: [DataDirect][ODBC SQL Server Driver]Datetime field overflow
Where is the date datatype being set so it does NOT default to smalltimedate? Could this be an ODBC setting for Date?
Any help will be greatly appreciated!
Thanks,
Joy
How to change Date datatype so it is NOT "smalldatetime
Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett, rschirm, roy
-
McKBISupport2
- Premium Member

- Posts: 1
- Joined: Thu Mar 27, 2008 3:25 pm
Return to “IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration