Jul 272016
 

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

Sorry, the comment form is closed at this time.

%d bloggers like this: