November 11, 2005 at 8:23 am
I'm using SSIS to move data from Oracle to Sql Server. One of my packages started dying on a date field. After further investigation, I had a date field in Oracle with the value May 16, 203. Obviously there was a data entry error on the Oracle side because the year should be 2003 and not 203. However, my SSIS package blew up saying "Conversion failed because the data value overflowed the specified type.". Upon further investigation, Sql Server datetime datatype has a range of January 1, 1753, through December 31, 9999.
I successfully used a Script Component to do a check on this field so I could trap the bad fields. But is there a workaround this in the database? A different datatype? What if you need to store dates less than the year 1753.
November 12, 2005 at 11:22 pm
From 15seconds.com http://www.15seconds.com/faq/Data%20Access/767.htm
1: Store as a Float.
2: Store as a canonical string, i.e. YYYYMMDDHHNNSSmmm (separators optional). Perform explicit conversion to an from the database.
3: Store Year, Month, Day, and TimeOfDay as individual values and provide your own date arithmetic.
4: Store as a Date, but also store a YearOffset value.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
November 14, 2005 at 8:55 am
Thanks for the info Kathi.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply