I am trying to import a date column from a table in Excel 2003 in SQL Server 2005 using SSIS. I am in the UK, so I want the dates to be formatted as dd / MM / yyyy.
Unfortunately, the column in the spreadsheet contains a mixture of dates stored as rows in dd / MM / yyyy format (with Excel formatting "General"), as well as dates using the Excel formatting "Date" dd / MM / yyyy (with language version "English (UK)").
This is as it is, and I cannot expect users to sort it out themselves.
When viewing the table, all dates are visually displayed correctly, i.e. dd / MM / yyyy.
I am trying to import values into a varchar column in a storage table in a database. Then I run the stored procedure, which copies these values into the correct table containing the datetime column.
The problem is that dates that are stored in a spreadsheet and use date formatting are imported as MM / dd / yyyy in SQL Server, and dates stored as strings are imported as dd / MM / yyyy. I have IMEX = 1 in the connection string.
Having dates using both formats in the same varchar column obviously causes a problem when I try to put it in a datetime column, therefore
SET DATEFORMAT MDY;
SET DATEFORMAT DMY;
useless.
Does anyone know why the SSIS package imports the seemingly correct dates in an Excel spreadsheet in SQL Server, like MM / dd / yyyy, anyway?
SSIS dd/MM/yyyy, ?
?!
,