-1

I have an issue concerning conversion in SSIS. I'm trying to convert StartDATE from DT_WSTR to Datetime2 (for SQL Server)

My date originaly looks like this 20140804 but I need to convert it to Datetime2 in such format 2014-08-04 00:00:00.0000000.

What I've done earlier with the StartDATE Column is:

RTRIM(DATSTHYRA)

Since I need to remove blank spaces...

I figured I can use the already Derived Column and add a new expression to convert it to Datetime2 but I'm running into issues and can't really find a topic online that covers my issue.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
user3052850
  • 178
  • 3
  • 17
  • It's easier to make the conversion in your SQL statement. The `YYYYMMDD` format can be converted to DATE or DATETIME directly, without localization issues. You could use `cast('20140804' as datetime2) ` – Panagiotis Kanavos Sep 07 '16 at 13:37

2 Answers2

0

You can do it in a single step. Add Derived Column transformation - transform your YYYYMMDD string to YYYY-MM-DD with SUBSTRING functions and then - cast to DT_DBTIMESTAMP2 with scale needed. This would yield an expression like 

(DT_DBTIMESTAMP2, 7)(SUBSTRING([StartDATE],1,4) + "-" + SUBSTRING([StartDATE],5,2)  
 + "-" + SUBSTRING([StartDATE],7,2))

Then configure Error Output on this Derived Column transformation to capture and handle conversion errors.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • The unseparated date format is unambiguous and can be converted to date or datetime directly. This code though is culture specific and can fail. – Panagiotis Kanavos Sep 07 '16 at 13:33
  • @PanagiotisKanavos, string to be casted to SSIS Datetime has to be in format YYYY-HH-MM hh:mm:ss.nnn. From my experience, it is Culture neutral and does not depend on Server locale. – Ferdipux Sep 07 '16 at 13:36
0

In SSIS, you can use data conversion transformation, the data type mapping is database timestamp with precisionin SSIS is for datetime2 in SQL Server.

Dance-Henry
  • 923
  • 1
  • 7
  • 11