I'm running a job to DTS data from SQL 2005 to SQL 2000. This is actually running across 13 servers without any problem.
The date format is change in one server since October 1st, which was working in September.
I'm using Export Wizard and exporting the data into SQL 2000. I get the date format as mm/dd/yyyy
I then save that exact Export as SSIS package (saved under the DB and file) and then schedule it to run. It runs fine but the date is formatted as dd/mm/yyyy
I'm doing a simple select CONVERT(varchar, dbo.table1.field1, 101) AS [Date] and Inserting into a DATE field on the destination server which is smalldatetime.
Again, I'm running the exact process on 13 other servers without an issue. The only difference between this server and the other servers is the OS. Other servers are running SQL 2005 on Windows 2003 R2 64bit and this one is running on Windows 2008 R2.
Just to add that I verified the Date format on the OS level and it is set to M/dd/yy. I actually cross checked that with the other servers.
I'm not sure that how this was working fine in September and now comes October, the date format is changed.
Thanks in advance for your help.