How to convert default Date format from MM/DD/YY to YYYY/MM/DD HH:MM:SS in SSIS packages?

Question

I don't know what to write in Derived column expression.

Steps- 1.Source - Excel file 2.Derived column 3.Destination - OLE DB Destination

Answer 1

If you have the date in a string value, and know it is truly MM/DD/YY not M/D/YY (i.e. will have zero padding as needed so 2022-01-01 will be sent as 01/01/22 not 1/1/22), and you want a string value out, then you just need to use the SUBSTRING function to pull out the right characters to form a new string. If you might get years before 2000 then you'll need a conditional to decide is you are concatenating 19 or 20 to make YYYY from YY. There are a number of answers here and on StackOverflow and articles elsewhere covering that in sufficient detail.

If you want to generate an actual date value (a value of a type like DT_DATE or DT_DBTIMESTAMP) from such a string, just use a Data Conversion task to convert to data, and make sure that the locale is set correctly (open the advanced properties for the conversion step and you'll find it under component properties / common properties, called LocaleID, set it to "English (United States)" for MM/DD/[YY]YY).