How Do I Convert a Y2K Date to SQL DATE In SSIS?

问题:

I am using SSIS (SQL 2008) to bring data over from an AS400. The date values are stored in the 400 as a 7 digit numeric. Here is the format: "CYYMMDD" C is a "century digit" where 0 = 1900 and 1 = 2000. I have been looking into derived columns and script components. I am very new to SSIS and all the casting required compounded with different cases is making me a dull boy. Also, I am losing leading zeros. I am not sure if that is b/c they are numeric type and I would see them correctly if I cast as string or not. Below is what I am seeing in SQL after a direct pull from the 400 using SSIS.

AS400   =   Actual 
101         01/01/1900 (I think these are "unknown" dates)
1231        12/31/1900 (I think these are "unknown" dates)
20702       07/02/1902
151231      12/31/1915
1000102     01/02/2000
1110201     02/01/2011

回答1:

You should be able to use this expression

(DT_DBDATE) ((DT_STR) (AS400 + 19000000))

回答2:

Firstly, add the leading zero's in a derived column task:

RIGHT("000000000" + (DT_STR,10,1252)AS400,7)

Pass this to another Derived column task, and use an expression to perform the conversion depending on the century digit, something like:

SUBSTRING([Derived Column 2],1,1) == "0" ? (DT_UI4)[Derived Column 2] + 19000000 : (DT_UI4)SUBSTRING([Derived Column 2],2,8) + 20000000

Which should give you something like 20110201. You can then convert this, or shred it into date parts as required.

回答3:

Neither of the two answers were 100%, but both helped me to figure out the prob. Not sure whom to mark as "correct" Here is what I did. Had to do 2 derived columns.

1. ((DT_WSTR,8)(<<AS400>> + 19000000)) 
2. (DT_DBDATE)(SUBSTRING(DCDateString,1,4) + "-" + SUBSTRING(DCDateString,5,2) + "-" + SUBSTRING(DCDateString,7,2))

回答4:

select substring(t1.datefield,4,2)|| '/' || substring(t1.datefield,6,2) || '/' || (cast(substring(t1.datefield,1,3) as integer) + 1900) as RegularDate from db.table1 t1

回答5:

If I recall correctly, the century mark date didn't go back to 1900, but rather had to do with an arbitrary date. You might want to check the AS400 redbooks related to the Y2K dates. I programmed on the AS400 from 1992 - 2005. The break years are 1939 & 2039. Date before or after these respectively fail under the century mark system. This is because IBM decided that any two digit year greater than 39 referred to the 1900's, anything less than or equal to 39 referred to the 2000's. If you are dealing with future dates, this might cause a snag.