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

Who is Participating?
Is it feasible to specify the format explicitly in your SQL?  

CONVERT(DateTime,''2009-12-31', ,120)
Aneesh RetnakaranDatabase AdministratorCommented:
select convert(datetime,'2009-12-31',120)
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.
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.