Today I had the “pleasure” to fix a problem in a customer’s SQL server database where some records had wrong values in date fields. The theory is quite simple. Find out which records are affected (there were only 7 of them) and correct the problem with a statement like this:
update tablename
set datefield='2008-02-14'
where someotherfield='somestringvalue'
Unfortunately the SQL server complained that it could not convert the string to a date. Google didn’t really help much because apparently it works like this for everybody else but me. But I found a hint how to test it quite simply:
select isdate('2008-02-14')
After a bit of try and error if found the problem:
The server did not actually assume a date of the form yyyy-mm-dd to be ISO 8601 as every human probably would. It assumed it to be yyyy-dd-mm so
select isdate('2008-14-02')
worked.
Americans! With yy/dd/mm You have given the world the most stupid date format ever, but Microsoft managed to top even that with yyyy-dd-mm.
So, eventually I used
update tablename
set datefield='2008-14-02'
where someotherfield='somestringvalue'
and it worked.
EDIT:
As Stefan Glienke pointet out in this Google+ post the order of y,m,d in a date is configurable with SET DATEFORMAT
So apparently on the machine I was working on it was set to
SET DATEFORMAT ydm
and I could have fixed the problem with
SET DATEFORMAT ymd