How to convert YYYYMMDD to datetime using SSIS Data Conversion?

Question

Post reply

How to convert YYYYMMDD to datetime using SSIS Data Conversion?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

banu2316 SSC Enthusiast Points: 130 More actions · Answer 1

How to convert 20 jan 2012(string) into 2012-01-20 using derived column?

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

banu2316 (2/14/2012)
How to convert 20 jan 2012(string) into 2012-01-20 using derived column?

I don't know how to do it in SSIS but here's how it can be done in T-SQL.

SELECT CONVERT(CHAR(10),CAST('20 jan 2012' AS DATETIME),120)

In truth, it's better to NOT format dates in T-SQL or SSIS if you can help it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

banu2316 (2/14/2012)
How to convert 20 jan 2012(string) into 2012-01-20 using derived column?

I wouldn't do this in SSIS. It is much much easier in TSQL as Jeff has demonstrated.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ken SSCoach Points: 17191 More actions · Answer 4

JustMarie (2/18/2009)
OK - here's the short answer on how I did this.
Create a Derived Column transformation, create a new column, and put this in the expression field. Use your own field name, obviously.
(DT_DATE)(SUBSTRING([field],1,4) + "-" + SUBSTRING([field],5,2) + "-" + SUBSTRING([field],7,2))
Personally I name the derived column as CONV_[field] just so I know it's a converted field and what field I converted. Use whatever naming convention you like.
Use the derived column in your destination mapping.
Longer answer.
Make sure you have your source coming in as a [DT_STR] data type.
Even longer answer.
If you're setting this up for the first time and creating the table, here's the way I found it most convenient to get things correct in the long run.
When creating your flat file data connection set the data type to [DT_DBTIMESTAMP] for all the fields that have the YYYYMMDD format. After you get all the fields properly formatted create an OLE DB destination and connect the two. Make sure you have your OLE DB connection manager in place. Select the connection manager and next to the field for 'Name of the table or the view' click on the New button. You'll get a window with the code to create the table.
Personally, I copy this code and bring it over to the Management Studio and put it in a query window. Put in your schema, desired table name, add the values for your decimal fields, etc. Run the query to create the table.
Now go back and edit the flat file connection manager. Change all the date fields to [DT_STR] and change the output column width value to match the input column width, as the value will default to 50 when you change the data type.
Disconnect the source from the destination and then put your transformations in place. Convert the date values as above and when you finally do connect the destination change your mapping to the converted/derived column.
Hope this helps.

I just had to do this same function by stripping the YYYYMMDD value off a file name to use as a database value. Thank you so much for this solution. It worked perfectly!

JustMarie SSCertifiable Points: 7771 More actions · Answer 5

Wow. Just wow. I wrote that over six years ago and it's still helping people.

I'm kinda choked up right now. And I'm also glad that I took the time to write a full solution with explanation so it stays valid after all these years.

Ken SSCoach Points: 17191 More actions · Answer 6

JustMarie (10/15/2015)
Wow. Just wow. I wrote that over six years ago and it's still helping people.
I'm kinda choked up right now. And I'm also glad that I took the time to write a full solution with explanation so it stays valid after all these years.

Thanks again! Imagine that, doing it the right way makes a difference! 😀 I think there is a lesson in here somewhere...