I am importing data from a csv file. All data is loaded as VARCHAR (50) to the staging table. Amongst other columns I have to convert dates to ‘datetime’ data type. I do this by using a Derived Column Transformation where I use this expression: (DT_DBTIMESTAMP)(SUBSTRING([Applicable Date],5,2) + "/" + SUBSTRING([Applicable Date],7,2) + "/" + SUBSTRING([Applicable Date],1,4)) These are my dates before conversion which ideally should translate to 10/04/2011 and 12/04/2011 respectively: 20110410 20110412 After conversion in the final destination table I get this: 04/10/2011 00:00:00 04/12/2011 00:00:00 Before the data is loaded to the final destination table it passes through a SCD Type 1. I don’t know if this is relevant to the strange results. I can’t figure out what exactly is going wrong. Some of the dates in same format (in the same file) before conversion come out correctly as expected in the final destination table. This thing is skewing my data a big time. I’ve spent 2 days trying to figure out what exactly could be happening. Many thanks. Mpumelelo

Can't you just switch the day and month part in your expression? Or do I misunderstand you? (DT_DBTIMESTAMP)(SUBSTRING([Applicable Date],7,2) + "/" + SUBSTRING([Applicable Date],5,2) + "/" + SUBSTRING([Applicable Date],1,4)) And instead of using the SCD component you could use the much faster lookup alternative: http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html EDIT: indeed its saver to use the iso format. Something like: (DT_DBTIMESTAMP)(SUBSTRING([Applicable Date],1,4) + "-" + SUBSTRING([Applicable Date],5,2) + "-" + SUBSTRING([Applicable Date],7,2)) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Many thanks SSISJoost. Your suggestion of switching the month and the day looks like it has worked. Thanks also for the suggestion about SCD. I will look into it by and by.Mpumelelo

I would STRONGLY recommend that you don't rely on this moving forward. MM/dd/yyyy and dd/MM/yyyy are ambiguous date formats that depend on the regional settings of your machine. Use the ISO/SQL standard format of yyyy-MM-dd, and you'll always get the results you expect on ANY machine. Talk to me now on

Thank you Todd. Very useful advice indeed especially to someone like me who is still growing in developer experience. The link that you provided is rich with information not only relating to my question but also touching on many aspects relating to date issues. Wonderful post indeed. Mpumelelo