2

I have a date 26.12.2019 (dd.mm.yyyy) in CSV which I'm trying to convert to 2019-12-26 using Derived Column in SSIS. I have used this expression but it does not seem to work.

(TRIM([Period Start Date]) == "") ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([Period Start Date],7,4) + "-" + SUBSTRING([Period Start Date],4,2) + "-" + SUBSTRING([Period Start Date],1,2))

How do I rewrite this expression to produce the correct output with a value of data type?

Hadi
  • 36,233
  • 13
  • 65
  • 124
prajakt dixit
  • 21
  • 3
  • You cannot change the format of a date. However, you can do this as a string data type. – KeithL Oct 29 '19 at 15:56
  • What does "does not seem to work" mean? If you received an error message, please edit your question and include the message. – Eric Brandt Oct 29 '19 at 21:34

1 Answers1

1

When using DT_DATE data type, value is not saved in a specific format, it is shown based on your operating system regional settings. If you need to use a specific format, you can keep it as string as following:

(TRIM([Period Start Date]) == "") ? NULL(DT_WSTR,50) : (DT_WSTR,50)(SUBSTRING([Period Start Date],7,4) + "-" + SUBSTRING([Period Start Date],4,2) + "-" + SUBSTRING([Period Start Date],1,2))
Hadi
  • 36,233
  • 13
  • 65
  • 124