Convert String into date time

Question

Hi All , i am loading data from a .csv file into a sql server table. The incoming date is in the format "29052008" , i am trying to get into the format "2008-05-29 00:00:00.000". I used a derived column and used the expression ((DT_DBTIMESTAMP2,7)DtStart). The load is failing the following errors
[Build Output Record [32]] Error: An error occurred while attempting to perform a type cast.

[Build Output Record [32]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Build Output Record" failed because error code 0xC0049064 occurred, and the error row disposition on "Build Output Record.Outputs[Derived Column Output].Columns[InactiveStartDateConverted]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Please advice.
Thansk

Answer 1

A DT_DBTIMESTAMP2 convert requires a datetime string in format yyyy-mm-dd hh:mm:ss[.fffffff] as described in the documentation reference. Try using SUBSTRING and concatenate the date parts from the source field into the needed format, like:

((DT_DBTIMESTAMP2,7)(SUBSTRING(DtStart,5,4)+"-"+SUBSTRING(DtStart,3,2)+"-"+SUBSTRING(DtStart,1,2)+" 00:00:00.0000000"))  

Answer 2

Hi @bk ,

Perhaps it exists null value, following expression for your reference:

(TRIM(DtStart) == "") ? NULL(DT_DBTIMESTAMP) : ((DT_DBTIMESTAMP2,7)(SUBSTRING(DtStart,5,4) + "-" + SUBSTRING(DtStart,3,2) + "-" + SUBSTRING(DtStart,1,2) + " 00:00:00.0000000"))  

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October