title: Date format problem using SSIS for Excel into SQL Server
body: <p>I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy. </p>
  
  <p>Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings in dd/MM/yyyy (with Excel 'General' formatting) as well as dates using Excel 'Date' formatting dd/MM/yyyy (with locale 'English (United Kingdom)'). </p>
  
  <p>This is just the way it is and I can't expect the users to be able to sort this out themselves.</p>
  
  <p>When looking at the spreadsheet, all of the dates visually appear correct i.e. dd/MM/yyyy.</p>
  
  <p>I am trying to import the values into a varchar column in a holding table in the database. Then I run a stored procedure that copies these values into the proper table which contains a datetime column.</p>
  
  <p>The problem is that the dates that are stored in the spreadsheet and use Date formatting get imported as MM/dd/yyyy into SQL Server and the dates stored as strings are getting imported as dd/MM/yyyy. I have IMEX=1 in the connection string.</p>
  
  <p>Having dates using both formats in the same varchar column is obviously causing a problem when I try to put it into a datetime column, hence</p>
  
  <pre><code>SET DATEFORMAT MDY;    
  SET DATEFORMAT DMY;
  </code></pre>
  
  <p>are of no use.</p>
  
  <p>Does anyone know why the SSIS package would import the seemingly correct dates in the Excel spreadsheet into SQL Server as MM/dd/yyyy anyway?</p>
  
  <p>Is there anyway to force the SSIS package to import the dates as dd/MM/yyyy that will work with this combination of dates as strings and cells with date formatting applied?</p>
  
  <p>Any other ideas?!</p>
  
  <p>Many thanks,</p>
  
  <p>Anthony</p>