SSIS converting Time Stamp format using derived co

Question

I have a timestamp in a text file that looks like this: 7/2/2013 17:40:22

I need to convert it to this: 2013-07-02 17:40:22.913

Using a derived column, I tried this: (DT_DBTIMESTAMP)TIME_STAMP and

(DT_DBTIMESTAMP)(SUBSTRING(TIME_STAMP,1,4) + "-" + 
SUBSTRING(TIME_STAMP,5,2) + "-" + 
SUBSTRING(TIME_STAMP,7,2))

But I am getting an error: Conversion between types DT_STR and DT_DBTIMESTAMP is not supported.

Thanks!

Answer 1

using token will do ... assume your timestamp is in a string(WSTR) format

TOKEN([Order Date],"/",3) + "-" + TOKEN([Order Date],"/",1) + "-" + TOKEN([Order Date],"/",2)

Answer 2

If you must go the route of casts, let me know and I'll edit this answer to reflect it. Otherwise, simplify your life and fix your Flat File Connection Manager. By default, it's going to assume every column coming in is varchar(50). This gets tricky with dates and times because well known formats that should just convert require far too much expression garbage to make them work.

In your case, change the type to DT_DATE I know you'd think it's for dealing date only but that'd be DT_DBDATE.

By simply changing to that data type, I was able to import your value without issue.