0

I have to convert timestamp which is in varchar (3/12/2021 1:38:53 PM) to datetime using SSIS. This is code I used to do the same

(DT_DBDATE)(SUBSTRING(TimeStamp,FINDSTRING(TimeStamp,"/",2),4) + "-" + (LEN(SUBSTRING(TimeStamp,FINDSTRING(TimeStamp,"/",1),FINDSTRING(TimeStamp,"/",2)))==1?"0"+SUBSTRING(TimeStamp,FINDSTRING(TimeStamp,"/",1),FINDSTRING(TimeStamp,"/",2)):SUBSTRING(TimeStamp,FINDSTRING(TimeStamp,"/",1),FINDSTRING(TimeStamp,"/",2))) + "-" + (LEN(SUBSTRING(TimeStamp,1,FINDSTRING(TimeStamp,"/",1)))==1?"0"+SUBSTRING(TimeStamp,1,FINDSTRING(TimeStamp,"/",1)):SUBSTRING(TimeStamp,1,FINDSTRING(TimeStamp,"/",1))))

The above threw the following error:

[Derived Column [2]] Error: An error occurred while attempting to perform a type cast.

I would also need to include the time as my destination SQL column is DATETIME2.

Vaishnave S
  • 3
  • 3

1 Answers1

0

Without manual parsing method:

From microsoft docs:

When a string is cast to a DT_DATE, or vice versa, the locale of the transformation is used.

Go into derived column advanced setting and set LocaleId to something appropriate for your date source. LocaleId of "English (United States)" should work based on the example you provided.

Then leverage DT_DATE to do the parsing before casting it to DT_DBDATE

(DT_DBDATE)((DT_DATE)TimeStamp)
vhoang
  • 1,349
  • 1
  • 8
  • 9