I love SQL Server and cultures... NOT !

When developing a large system, all is not unicorns and rainbows.

For now, everybody was working on a single SQL dev server and friction is high.

That’s why I’m working on SQL scripts management with mercurial and powershell to the rescue, so that any developer can trash his own SQLExpress instance, and rebuild everything needed in a single command. (I’ll maybe blog about all that later).

We have loads of stored procs.. I know people don’t like it, but it acts as a strong sanity layer when the database schema is so ugly your eyes bleed when you look at it.

Yesterday, I run a stored proc, and I get the following error :

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Why the f**k.

The procedures is using a scalar function :

ALTER FUNCTION [dbo].[DateMaxValue]()
RETURNS datetime
AS
BEGIN
RETURN '9999-12-31 23:59:59.998'
END

It’s working on other servers.. why doesn’t it work here.

After several tries, I try with the date ‘9999-12-01’ and I get the following date:

Year: 9999

Month: 01

Day: 12

Yes.. the date is interpreted as YYYY-dd-MM on a French server.

Even when you use the YYYY-??-?? format, SQL Server still try to mess with culture month/day ordering !

You can use the SET DATEFORMAT dmy or SET DATEFORMAT mdy to change this, but it will apply only in current session, and you cannot use it in a stored proc.

You can change the server culture, but it wont change anything. The dmy/mdy setting is ultimately in the Login culture.

You read it right :

• For an English Login the function above works.
• For a French Login the function above fails miserably.

There is no way to my knowledge to specify a strict date parsing in a stored proc or function.

So generates your logins with scripts, and enforce the same culture for all logins.

It’s just profoundly broken.