How to Convert a CYYMMDD Date in SSIS

If you have ever had to extract a date field from DB2 where the date format follows a CYYMMDD format because of Y2K, you probably understand some of the challenges of getting that in to a usable date field in SQL. In our environment the DB2 CYYMMDD was stored as a decimal, we needed to transform this to conform with a Date data type in SQL. Here is how we do it.

First you need to specify your data source in SSIS, this will be the DB2 data source. Once you have your data in the pipeline you'll want to create a Derived Column transform. Under the "Derived Column" drop down select "add as new column". In the Derived Column Name provide a name for your column, in this example DC_ReleasedDate. In the Expression field type the following expression:

(DT_DATE)(ReleasedDate == 0 ? "1900/01/01" : (SUBSTRING(((DT_WSTR,10)(ReleasedDate + 19000000)),1,4) + "/" + SUBSTRING(((DT_WSTR,10)(ReleasedDate + 19000000)),5,2) + "/" + SUBSTRING((DT_WSTR,10)(ReleasedDate + 19000000),7,2)))

You can replace "ReleasedDate" with your own DB2 date data field. In the above expression, we are creating a default date of "1900/01/01" if no date exists but this part can be removed if you need.

Where you see (DT_DATE) at the beginning of the expression, that is casting the expression as a date. Following that you see ReleasedDate. ReleasedDate is the name of our date field in DB2. We are looking for any dates that = 0 and if a date = 0 then it is replaced with "1900/01/01" (a string), if the date does not = 0 then it adds 19000000 to the decimal date and then converts it to a string (DT_WSTR). We add the 19000000 so we can get accurate dates from both 0 and 1 century fields, the c in cyymmdd. Next we are using SUBSTRING to get the yyyy. We do the same for month and day while concatenating each with a forward slash. The end result is a string of yyyy/mm/dd that is cast as a date, where we started in the expression. You can also accomplish this in a script task but for me I found this to be easier.

The destination table in SQL has a [Date] data type since we don't have a timestamp in DB2, only date. You could still work the same solution with a timestamp, it would just require a little extra work in the expression.