title: How to save date value in dd/mm/yyyy into excel from SSIS where TimeZone=Eastern in server
body: <p>I have an SSIS package, which I am running a Stored Procedure and save the resultset in a object type of variable (as temptable) in the control flow, and in the data flow I am using a script to retrieve data from the object type of variable saving the result set in an excel file.</p>
  
  <p>Everything works well except the date column. It should be in dd/mm/yyyy format, however whatever I have tried I could not prevent it to be in m/d/yyyy format in the excel.</p>
  
  <p>What I have tried are:</p>
  
  <p>1- Added this to connectionstring of Excel ConnectionManager -> <code>Excel 12.0;HDR=Yes;IMEX=0;Readonly=False</code> and <code>Excel 12.0;HDR=Yes;IMEX=1;Readonly=False</code>. Both did not work.</p>
  
  <p>2- Added a Derived Column task, derived a new "DerivedMyDate" column as <code>STR</code>, <code>WSTR</code>, <code>Date</code>. Used this column to save the date value in the excel file, where type is set as <code>STR</code> and <code>DATE</code>. None of my trials worked.</p>
  
  <p>3- Added Data Conversion task, tried all same things that I've tried in 2nd step, none worked.</p>
  
  <p>4- Added a dummy first line in the excel, where the data column is like <code>15/01/1900</code>. Does not work, it is adding the other rows below where date values in <code>m/d/yyyy</code> format.</p>
  
  <p>Checked those pages, the answers did not work for me:
  <a href="https://stackoverflow.com/questions/1043483/date-format-problem-using-ssis-for-excel-into-sql-server">Date format problem using SSIS for Excel into SQL Server</a></p>
  
  <p><a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e34a0af-a744-4a51-b925-3284d0fba296/problem-in-date-format-while-exporting-to-excel-destination-from-flat-file-source-in-ssis?forum=sqlintegrationservices" rel="nofollow noreferrer">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e34a0af-a744-4a51-b925-3284d0fba296/problem-in-date-format-while-exporting-to-excel-destination-from-flat-file-source-in-ssis?forum=sqlintegrationservices</a></p>
  
  <p>TimeZone of my PC: (UTC-05:00) Eastern Time (US &amp; Canada), changing this is not an option.</p>
  
  <p>Any help or advice would be appreciated.</p>