none
Convert the date in dd-mm-yyyy format in excel to mm-dd-yyyy format in SSIS

    Question

  • I have an ssis package which reads an excel file. This excel has a date column in the format "dd-mm-yyyy". But when i try to insert into the sql server table, it saves it in the mm-dd-yyyy format. How can i convert the date field in excel to mm-dd-yyyy format before inserting in the data transformation task ? i have tried writing expression in derived column task uising substring and date part functions, but nothing worked. All it did was it threw errors saying it failed during type cast or conversion. Please let me know how to solve this. Thanks in advance.
    Tuesday, September 27, 2011 7:21 AM

Answers

  • Hi,

    Please try this expression in your Derived Column


    (DT_STR,4,1252)DATEPART("mm",[Copy of Date]) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",[Copy of Date]),2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("yyyy",[Copy of Date]),4)

    Hope this helps!!


    Sharath
    Tuesday, September 27, 2011 7:52 AM

All replies

  • Hi,

    Please try this expression in your Derived Column


    (DT_STR,4,1252)DATEPART("mm",[Copy of Date]) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",[Copy of Date]),2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("yyyy",[Copy of Date]),4)

    Hope this helps!!


    Sharath
    Tuesday, September 27, 2011 7:52 AM
  • What's the data type of the column DT_DATE or DT_WSTR?
    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Tuesday, September 27, 2011 7:58 AM
    Moderator
  • It DT_DATE
    Niranjan
    Tuesday, September 27, 2011 8:31 AM
  • Sharath, when the excel connection manager reads the excel, the date column is erad as "yyyy-dd-mm"(This is what is happening in my case).  When tour expression executes it get the date part in the column and considers it as month. I have tried this. Anyways, i appreciate your help. If you have anything else please share it.


    Niranjan
    Tuesday, September 27, 2011 8:57 AM
  • Can you use a data conversion to convert the data coming from excel to date. Then you use the above expression? Have you tried that ?
    Sharath
    Tuesday, September 27, 2011 9:08 AM
  • Can you use a data conversion to convert the data coming from excel to date. Then you use the above expression? Have you tried that ?
    Sharath

    The data from the excel itself is coming in as date. If i try to use substring to get the date, month and year parts, even it is giving conversion error. Or may be i am not using it prroperly.  I am not quite sure.
    Niranjan
    Tuesday, September 27, 2011 9:24 AM