Share via

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

Micki Arruda 1 Reputation point
2021-04-29T13:50:21.357+00:00

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
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,731 Reputation points
    2021-04-29T15:53:03.367+00:00

    Hi @Micki Arruda ,

    (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

    0 comments
  2. ZoeHui-MSFT 35,556 Reputation points
    2021-04-30T02:29:11.063+00:00

    Hi @Micki Arruda ,

    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

    0 comments