strange date issue in SQL Server 2005 Express

[agrothe]

Note: I debated whether this should go in ASP forum or SQL Server forum and I think it belongs here.

Ok, I have a classic ASP web application, a custom intranet, which has been used in 7-8 different installs, 3-4 different servers with no issues.

It uses a SQL Server 2005 database and usually MS Server 2003 and has dates heavily used throughout. Adding various items (news, announcements, careers, events, etc, etc) has never been an issue.

I recently had to install this on SQL Server 2005 Express and Windows 2000 Professional Terminal Server.

For some reason now, a date of 2/1/2009 gets saved as 1/2/2009. I had a look at the date format on the Win2000 box, which was set to dd/mm/yyyy as opposed to the usual mm/dd/yyyy that all the other servers had that this app was installed on. So, I changed the date format to mm/dd/yyyy, rebooted and still the dates get screwed up!

I have verified that all databases, logins, etc have a locale of us_english. The locale hasn't changed from the original SQL Server to the Express edition.

I can fix this by changing the code to

rs("field") = month(date) & "/" & day("date") & "/" & year(date)

But that's a lot of hunting through thousands of lines of code to change. Is there a setting somewhere that I'm missing?

[sknake]

This is the exact reason you should be using parameterized SQL so regardless of the date format this would not be an issue. See http://www.daniweb.com/forums/post811239-3.html for an example.

But to answer your question -- I have looked around myself and can't see a single setting where this would be changed system wide. More than likely, knowing microsoft, it defaulted a setting deep in SQL Server at installation time that will be next to impossible to find.

One solution may be to call SET DATEFORMAT MDY when you open up your connection, if you have a common method in your application for doing so. You're right that this is an SQL Server problem, but I think it looks like a back-handed asp.net fix. Other solutions I have seen are using ODBC drivers to connect to SQL Server to get around the locale settings, but that sounds worse than the problem.

Good luck :(

[agrothe]

This is the exact reason you should be using parameterized SQL so regardless of the date format this would not be an issue.

I could not agree with you more. This is a large code base that I've inherited from a previous developer so most of the code was already written....

For those newbies reading this, parametrized queries / stored procedures should ALWAYS be used, not only for an issue like this, but also for safety reasons, like preventing SQL Injection attacks, etc.

However, I think my only choice after all the research I've done, is to hunt through the code base and change the code. I've tried the set date format and it doesn't always work as desired, and there is only a common connection string, no common connection object....

Which also brings out the point that programs, desktop, web or otherwise which access a database should have a solid database layer through which all SQL is passed... but I digress.