question

MickiArruda-2207 avatar image
0 Votes"
MickiArruda-2207 asked ZoeHui-MSFT answered

How to convert dt_str to a yyyy-dd date format with derived columns

I have a dt-str coming in from a text file. it is a 6 character date yyyydd. I need the destination to be date yyyy-dd. I tried the code below, but it adds an 01 for the day at the end, making it 8 characters.

(TRIM(YMPPD1) == "") ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(YMPPD1,1,4) + "-" + SUBSTRING(YMPPD1,5,2))


I appreciate the help with this.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @MickiArruda-2207,

(1) The DATE data type shall be always in the following format: yyyy-MM-dd.

It is based on the ISO 8601 standard: iso-8601-date-and-time-format

(2) You can check the official SSIS documentation too:

DT_DBDATE A date structure that consists of year, month, and day.

Link: integration-services-data-types

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @MickiArruda-2207,

As YitzhakKhabinsky-0887 said that the DATE data type should be yyyy-MM-dd.

So we just can convert the 6 character date yyyydd to DT-STR yyyy-dd.

Don't forget to click Accept Answer so that we could archive the case.

Regards,

Zoe


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.
Hot issues October








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.