none
Parsing a date RRS feed

  • Question

  • I have a csv file, there’s a admit date field with datetime as a value and in my output I need just date. How can I achieve that in SSIS using Derived column expression:

    Input CSV screenshot

    Thanks,

    Ali.

    Friday, October 4, 2019 4:40 PM

Answers

  • Hi Ali,

    SSIS Token() function could be handy for your scenario.

    Check it out here:

    “…One of the problems that ETL developers encounter are dates that SSIS is unable to parse. In almost all cases the pattern is easy to see, it is a challenge to write the parsing routine…”

    https://www.sqlmovers.com/ssis-token-function/

    Something along the following:

    (DT_DATE)(
    LEFT(TOKEN([adm_dt], "/", 3),4) 
    + "-" + RIGHT("00" + TOKEN( [adm_dt], "/", 1),2)
    + "-" + RIGHT("00" + TOKEN([adm_dt], "/", 2),2)
    )
    


    • Edited by Yitzhak Khabinsky Friday, October 4, 2019 7:10 PM
    • Marked as answer by aliahad Monday, October 7, 2019 1:07 PM
    Friday, October 4, 2019 5:58 PM

All replies

  • Hi Ali,

    Try to modify the expression in https://zappysys.com/blog/ssis-yyyymmdd-date-format-using-ssis-expression/ to suite your needs.


    Arthur

    MyBlog


    Twitter

    Friday, October 4, 2019 5:37 PM
    Moderator
  • Hi Ali,

    SSIS Token() function could be handy for your scenario.

    Check it out here:

    “…One of the problems that ETL developers encounter are dates that SSIS is unable to parse. In almost all cases the pattern is easy to see, it is a challenge to write the parsing routine…”

    https://www.sqlmovers.com/ssis-token-function/

    Something along the following:

    (DT_DATE)(
    LEFT(TOKEN([adm_dt], "/", 3),4) 
    + "-" + RIGHT("00" + TOKEN( [adm_dt], "/", 1),2)
    + "-" + RIGHT("00" + TOKEN([adm_dt], "/", 2),2)
    )
    


    • Edited by Yitzhak Khabinsky Friday, October 4, 2019 7:10 PM
    • Marked as answer by aliahad Monday, October 7, 2019 1:07 PM
    Friday, October 4, 2019 5:58 PM
  • Hi Ali,

    It seems that the data has one space between the date and time.

    We could use (DT_DBDATE)TOKEN(Date," ",1) in the expression of the Derived Column Transformation.

    Please see the following pictures:

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, October 7, 2019 6:15 AM