locked
Change Date format to Text for Import RRS feed

  • Question

  • I have used Sql Server Integration Services to extract some data into Excel.  When I load the files it seems that not all the time the dates are formatted with the time.  If I format the field this just changes the display value and not the underlying field value.  I have tried to copy the field into notepad, add a new field format this as text and then paste the desired format.  But this also does not always work.  Is there an easy way to change the date/time value so it is always in a consistent format to import into another system?  
    Tuesday, January 13, 2015 4:12 PM

Answers

  • Hi SBolton,

    If I understand correctly, you want to change a column from Date data type to Text while importing data to Excel.

    If in this scenario, we can use Data Conversion Transformation or Derived Column Transformation to convert the original Date data type to Text data type, then use the new column maps to the corresponding Destination column.

    The following screenshots are for your references:

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, January 14, 2015 9:21 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, this issue might to config with SSIS, I'll move your question to the SSIS forum

    https://social.technet.microsoft.com/Forums/en-US/home?forum=sqlintegrationservices

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Wednesday, January 14, 2015 8:08 AM
  • Hi SBolton,

    If I understand correctly, you want to change a column from Date data type to Text while importing data to Excel.

    If in this scenario, we can use Data Conversion Transformation or Derived Column Transformation to convert the original Date data type to Text data type, then use the new column maps to the corresponding Destination column.

    The following screenshots are for your references:

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, January 14, 2015 9:21 AM
  • whats the inherent data type of the field in the table?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Wednesday, January 14, 2015 9:32 AM
    Wednesday, January 14, 2015 9:32 AM
  • Can you give some sample data on how the data looks like in Excel?

    Which data type does SSIS use in the data flow for that column?


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, January 14, 2015 1:05 PM