This question recently came up on an internal SSIS mailing list:
We’re converting date strings to date columns using the Derived Column transform. We’ve noticed some weird conversion behavior with older dates, like ‘0001-01-01’. It seems that year values under 30 go to the year 2000, and 30 and over go to 1900. So SSIS converts the value ‘0001-01-01’ to ‘2001-01-01’, and ‘0030-01-01’ to ‘1930-01-01’. What can we do to fix this?
Setting up a simple derived column transform and converting the string to various date column types shows this behavior quite well.
The following three conversions (for DT_DATE, DT_DBDATE, and DT_DBTIMESTAMP):
End up looking like this:
It turns out it’s matter of the http://msdn.microsoft.com/en-us/library/ms141036.aspx you use, and the range of values it supports. As you can see from the data viewer capture, ‘0001-01-01’ is converted ‘2001-01-01’ for the DT_DATE column type, but DT_DBDATE and DT_TIMESTAMP retain the expected value.
The DT_DATE column maps to the http://msdn.microsoft.com/en-us/library/1yy4h318(VS.71).aspx , and we use its conversion functions internally. It is a smaller data type (meaning it takes up less space in your data flow buffer), but is designed to represent a different date range than the other two data types. If you’re dealing with a large range of dates, be sure to select the appropriate date column type (usually DT_DBDATE if you’re not concerned with time, DT_DBTIMESTAMP if you are, and http://msdn.microsoft.com/en-us/library/bb677335.aspx if you need the additional precision).
