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: ASP, .net, SQL, Database, Language
No comments:
Post a Comment