asked
Cast dd Mon YYYY to mm/dd/yyyy in SSIS
I have a source file which has a Data column as ddMonyyyy(10Mar2013), I need to convert this during my transformation, I can populate this into a staging and then do a convert on the column but I want to do it through SSIS, is there a way? Is using Script task the only way, if yes how can this be done.
You really should be using ISO 8601 date format.
http://en.wikipedia.org/wiki/ISO_8601
You can use a [derived column transformation][1] then reformat your date with the expression: datepart("year", yourcolumn) + datepart("month", yourcolumn) + etc... **-- Added based on follow up --** Since your source column is in a string format that SSIS cannot directly cast as a `DT_DATE` you'll need your derived column expression to massage the string prior to the cast by inserting some hyphens between the parts of the date: (DT_DATE)(SUBSTRING(datestring,1,2) + "-" + SUBSTRING(datestring,3,3) + "-" + SUBSTRING(datestring,6,4)) This assumes that you always have a three character month. Adjust as necessary :) After you do this, you can use the datepart approach outlined above (or just leave it as a date and it can be manipulated any number of ways once you have it in the database). [1]:
http://msdn.microsoft.com/en-us/library/ms141069.aspx
The issue here is the source column is a sting data type, when I try to change the data type in the Flat File Editor, I get an error stating that the data will be truncated. I used the above the solution with data type conversion in the derived column DATEPART("Year",(DT_DATE)DOB) + DATEPART("Month",(DT_DATE)DOB) + DATEPART("Day",(DT_DATE)DOB). This will not work as the type conversion will not processm when I try this I get a error An error occured while evaluating teh function
Refer this link. It has more date functions [Date Formats][1] [1]:
http://www.sql-server-helper.com/tips/date-formats.aspx
15 People are following this question.
