question

Usually, it is best if the formatting is done in Excel itself before loading the data, but if you need to do it in T-SQL for whatever reason then you can try to discard the ' to ' part and use the values to the left and to the right of it. If your dateformat setting is m/d/y (default) then the following should do it: select cast(substring(ExcelColumn, 1, charindex(' to ', ExcelColumn) - 1) as datetime) FirstDate, cast(substring(ExcelColumn, charindex(' to ', ExcelColumn) + 4, 50) as datetime) SecondDate from YourTable This way, you don't have to worry about how the day and month part is spelled out, the CAST will cast it regardless whether there is a leading 0 or not. Hope this helps. Oleg

You can make creative use of FINDSTRING, LEN, and SUBSTRING. FINDSTRING in SSIS acts much like CHARINDEX in T-SQL, and LEN will give you the total length, from which you can subtract various numbers of characters based on FINDSTRING (such as to find the end of the day elements). An example of this practice in action: http://stackoverflow.com/questions/18875997/ssis-how-to-get-part-of-a-string-by-separator-using-derived-column Though I'd suggest doing all of the manipulation in one derived column in the end, you may find it easier to assemble the logic using multiple derived columns one after the other to separate your dates, then to pull out the month, the day, and the year, and finally to reassemble them in yyyy-MM-dd and cast them to DT_DBDate or similar.