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 …http://toddmcdermid.blogspot.com.au/2008/11/converting-strings-to-dates-in-derived.html

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s