asked on

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

ASKER CERTIFIED SOLUTION

Paul_Harris_Fusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

Brendt Hess

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.

yuval08

ASKER

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