Month and day of imported datetimes are switched

Hello!

I′m not quite sure if this is the correct forum for this question but anyway:

I`m importing columns from an IBM Informix Server (7.31) with SSIS to SQL Server 2005 SP1. My problem is that in all datetime values where the day is <=12, month and day are switched, which of course gives me a false date. The date is imported as month/day/year.

Example:

real date: 01/24/2006 date in SQL Server 2005: 01/24/2006

real date: 01/02/2006 date in SQL Server 2005: 02/01/2006

I read that SQL Server always stores dates in the same way and only the output type is specified by the clients′ regional and language settings. But the dates are definitely stored false.

Any suggestions?

I believe that SQL Server's default interpretation of ambigious string dates is YMD.

You may need to use the DateFormat command prior to your data import in order to remove any ambiguities.

SET DATEFORMAT mdy

|||Hmm. The problem somehow dissappeared. I did the suggested DATEFORMAT and also changed the default Short Date Format to MM-DD-YYYY.