Regional Settings Issues

SQL Server 2000 has some interesting issues regarding it’s use of regional settings.
I had a slight problem with some date conversion issues.

If you wanted to convert a string to a date in transact sql typically you would use:

DECLARE @DATE DATETIME
SET @DATE  = ‘2005-03-15 09:46’

This should be 15th March 2005 etc

Since no default conversion has been specified a sane assumption would be that the machines regional settings would be applied. This is how windows applications are supposed to work.

I developed and tested this code on a standard US-English installation of SQL Server.
I even played with the regional settings and it worked just fine.

A customer installed it on a Spanish langauage version of SQL Server and the code broke.
They tried changing the regional settings to US-English and it still did not work.

SQL Server uses the language of the installation to determine the defaults for date conversion.
This is horrible from a testing perpective. I would need to install foreign language editions of SQL Server to test this – and I still can’t find this documented anywhere!

The solution to this problem is to specifiy the date format explicitly using the third parameter of the convert function:

DECLARE @DATE DATETIME
SET @DATE  = Convert(DATETIME,’2005-03-15 09:46′,121)

The moral of this story is don’t trust any automatic conversion always be specific.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s