I had a requirement to convert a string value which contains a date time value with the format as "dd-MMM-yy hh.mm.ss.nnnnnn a" to DateTime.
I initially tried to convert it with the millisecond value, but it throws an error.
For my requirement I do not need the milliseconds, therefore I tried to convert it by removing the milliseconds part and it worked.
Below is the Expression used in the Derived Column component
ISNULL(DATA_VALUE) || LTRIM(RTRIM(DATA_VALUE)) == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)((DT_WSTR,10)((DT_DBDATE)SUBSTRING(DATA_VALUE,1,9)) + (DT_WSTR,1)" " + REPLACE(SUBSTRING(DATA_VALUE,27,2) == "AM" ? (DT_WSTR,12)SUBSTRING(DATA_VALUE,11,8) : (DT_WSTR,2)((DT_I4)SUBSTRING(DATA_VALUE,11,2) + 12) + (DT_WSTR,10)SUBSTRING(DATA_VALUE,13,6),".",":"))
Hope this will be helpful.
I initially tried to convert it with the millisecond value, but it throws an error.
For my requirement I do not need the milliseconds, therefore I tried to convert it by removing the milliseconds part and it worked.
Below is the Expression used in the Derived Column component
ISNULL(DATA_VALUE) || LTRIM(RTRIM(DATA_VALUE)) == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)((DT_WSTR,10)((DT_DBDATE)SUBSTRING(DATA_VALUE,1,9)) + (DT_WSTR,1)" " + REPLACE(SUBSTRING(DATA_VALUE,27,2) == "AM" ? (DT_WSTR,12)SUBSTRING(DATA_VALUE,11,8) : (DT_WSTR,2)((DT_I4)SUBSTRING(DATA_VALUE,11,2) + 12) + (DT_WSTR,10)SUBSTRING(DATA_VALUE,13,6),".",":"))
Hope this will be helpful.
No comments:
Post a Comment