Error converting standarized datetime YY-MM-DD in T-SQL

I gave a sime sql date convertion from string formatted as YY-MM-DD, it works greate all of my clients and test computers, but fo some reasong we just updated a client of mine in Chile wich I can only guess they have a different regional settings, and I'm getting the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Using set dateformat is not an option and should not be a requirement, YY-MM-DD is a standart and for some reason that specific SQL Server in that country on working with YY-DD-MM.´

select convert(datetime,'2009-12-31')

Open in new window

yuval08Asked:
Who is Participating?

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

x
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.

Paul_Harris_FusionCommented:
Is it feasible to specify the format explicitly in your SQL?  

e.g
CONVERT(DateTime,''2009-12-31', ,120)
0

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
Aneesh RetnakaranDatabase AdministratorCommented:
select convert(datetime,'2009-12-31',120)
0
Brendt HessSenior DBACommented:
The problem is that 10-04-12 could as easily be Oct 4, 2012, or Apr 10, 2012 as it could be Apr 12, 2010.  To do this, you would need to actually use a CONVERT statement with the correct source date format.  Of course, this is complicated by the fact that yy-mm-dd is not a standard date format defined in the CONVERT context by SQL Server - it is recognized when the configured date format is compatible, but not otherwise.  To work around this, converting the date format to a standard format that can not be confused will be needed.

SELECT CONVERT(datetime, REPLACE('-', '', '10-04-12'), 12)

should do what you need.
0
yuval08Author Commented:
I still haven'd had the change to try it, I'll be back on the project on Monday, my initial test showed that It worked but I need to test it on my clients server in Chile...
0
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.