Saturday, December 3

SQL Server 2000 - Inserting dates in UK format

I've been moving a database to a new server. The ASP code that inserted dates passed a string in UK format (day-month-year) to the server.

This worked on the old server, but the new server was inserting and selecting on dates in month-day-year format, so the day and the month were interchanged.

It was difficult to find out how to change this. I found some pages suggesting using SET DATEFORMAT DMY, but this only worked for the current connection to the database. Eventually, I discovered that the date format was obtained from the language settings for the user the ASP code connected as.

I found this user using Enterprise Management / Security / Logins, and converted the settings from "English" to "British English".

Right clicking on properties gives:

I then reset the database and this worked without changing any of the ASP pages.

The following post describes how I swapped the dates that were incorrectly inserted.

Obviously it will be better in the long run to change the ASP code to use a language neutral format such as YYYYMMDD, to avoid this happening in future.

Technorati Tags: , , , ,

No comments: