Incorrect Date format SQL 2005 on Windows 2008 R2


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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Try sending it as format 112 or 120 instead of 101. Quite honestly, though, it looks strange to convert a DateTime column to VarChar simply to send to another DatTime or SmallDateTime column. What us the purpose of the conversion?
Kevin CrossChief Technology OfficerCommented:
By the way, the date format on the OS is not what matters, but what language the SQL server or a connection to it is set to. Therefore, if someone set the default language to British English On server or just for the connection, then it's default date format becomes 103 or DD/mm/YYYY ... Anyway, 112 is YYYYMMDD and 120 is YYYY-MM-DD HH:MI:SS ... These formats tend to work on both US and British English systems without error.
I agree with the above - format 112, without the varchar conversion, should do the job fine.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Anthony PerkinsCommented:
>>I'm doing a simple select  xCONVERT(varchar, dbo.table1.field1, 101) AS [Date] and Inserting into a DATE field on the destination server which is smalldatetime. <<
This should then be (fix the obvious typo in xCONVERT):
xCONVERT(smalldatetime, dbo.table1.field1, 101) AS [Date]
ezzadinAuthor Commented:
The destination server needs to get the date in mm/dd/yyyy format, no hh:mm:ss  and there is a Group By on the date, that is why CONVERT(varchar, dbo.table1.field1, 101) AS [Date]  is used.

This works on other servers and also works if I use Export Wizard. it is failing when SSIS is used.

Kevin CrossChief Technology OfficerCommented:
Okay, there are other ways to eliminate the time portion, but since you are familiar with CONVERT() -- use what I said which is 112 format code: CONVERT(CHAR(8), dbo.table1.field1, 112)
Kevin CrossChief Technology OfficerCommented:
To answer the question of why it fails in SSIS, the source or destination is likely set to a different default language (i.e., date format) and so it is processing based on that. Because you are converting to VARCHAR, the format matters as it has to do an implicit conversion back to SMALLDATETIME. If you strip out the time and send as a SMALLDATETIME, you probably won't get the issue: CONVERT(SMALLDATETIME, DATEDIFF(DD, 0, GETDATE()))

If you are indeed using SQL 2008, remember you have the DATE data type that you can use to make life easier also: CONVERT(DATE, GETDATE())

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ezzadinAuthor Commented:

Anthony PerkinsCommented:
>>The destination server needs to get the date in mm/dd/yyyy format<<
As you have discovered that is never a good idea.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.