Convert Incoming Flat File Date to DateTime in SSIS

You need to convert an incoming string formatted date from a flat file into a date datatype in SSIS.   The following example demonstrates how to convert a flat file date in the format “DD/MM/YYYY” into the ISO format YYYY-MM-DD, where it can be converted to a Datetime data type by DT_DBTIMESTAMP. Nulls and partially populated strings (e.g. a date without leading zeros formatted) are also handled by converting to null dates.

 (ISNULL([your date string])) ? NULL(DT_DBTIMESTAMP) : (LEN([date string]) < 10) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([your date string], 7, 4) + "-" SUBSTRING([your date string], 4, 2) + "-" + SUBSTRING([your date string], 1, 2))

Excellent reference here …

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s