SSIS DT_STR to DT_DBDATE using Derived column

Question

I have a fixed format .txt file which has a date field. This field may have a date or be blank. I have wracked my brains to get this field to convert from DT_STR to DT_DBDATE in the Derived Column transformation. I have tried multiple scenarios:

[CHNG_DT] == "" ?  ISNULL((DT_DBDATE)[CHNG_DT] ) : (DT_DBDATE)SUBSTRING([CHNG_DT], 1, 4)) + "-" + (DB_DBDATE)(SUBSTRING(CHNG_DT], 6, 2)) + "-" + (DB_DBDATE)(SUBSTRING([CHNG_DT], 9, 2))

[CHNG_DT] == "" ? NULL(DB_DBDATE) : (DB_DBDATE)[CHNG_DT]

ISNULL([CHNG_DT]) ? NULL(DT_DBDATE) : (DT_DBDATE)((DT_STR, 10, 1252)[CHNG_DT])

ISNULL(CHNG_DT) ? NULL(DT_DBDATE) : (DT_DBDATE)((DT_STR,10,1252)CHNG_DT)

LEFT([CHNG_DT], 10) == "          " ? ISNULL((DT_DBDATE)[CHNG_DT]) : (DT_DBDATE)[CHNG_DT]

(DT_DBDATE)CHNG_DT

Answer 1

First of all, when you are looking to convert values from DT_STR to DT_DBDATE, you should make sure that those values are stored in the yyyy-MM-dd format. You can refer to the following official documentation to learn more:

• Converting Between Strings and Date/Time Data Types in SSIS

The second suggestion is to use the LTRIM() and RTRIM() functions to make sure that there are no white blanks in the beginning and end of the date values. As an example:

LTRIM(RTRIM(REPLACENULL([CHNG_DT],""))) == "" ? NULL(DT_DBDATE) : (DT_DBDATE)LTRIM(RTRIM([CHNG_DT]))

If date values are stored in a different format, you should write an expression that changes the format before converting it to DT_DBDATE. As an example:

• How to convert string in format dd.mm.yyyy to date using SSIS expression?

Other helpful answers:

• SSIS Source Format Implicit Conversion for Datetime
• Convert DDMonYY and time to datetime column in SSIS package (Derived Column)
• The year function doesn't support dt_wstr