convert string date to datetime issue

Question

Hi ,

I want to convert my string date 20201022 to datetime stamp. getting an error

[Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" 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.

Answer 1

You can try the following SSIS Expression:

SUBSTRING(DateTransaction,1,2) + "-" + SUBSTRING(DateTransaction,3,2) + "-" + SUBSTRING(DateTransaction,5,4)

Answer 2

Hi @San ,

1.This example casts a string literal to the DT_DBTIMESTAMP2 data type that uses 4 digits for fractional seconds. (The DT_DBTIMESTAMP2 data type can have between 0 and 7 digits specified for fractional seconds.)
(DT_DBTIMESTAMP2, 4) "1999-10-11 16:34:52.1234"

Please refer to the following link and pictures:
Cast (SSIS Expression)

2.We can use the following expression in Derived Column:
(DT_DBTIMESTAMP)(SUBSTRING(DateTransaction,1,4) + "-" + SUBSTRING(DateTransaction,5,2) + "-" + SUBSTRING(DateTransaction,7,2))

Please refer to the following pictures:

Best Regards,
Mona

---

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.