Classic ASP Site Throwing Date Conversion Error After Moving Servers?

Question

I am moving an old store from a Win2003 IIS6 server to a Win2008 IIS7 server, moved everything across including database. The front end seems to work just fine, but when I login it has to do pull in data based on date ranges and now from no where I am getting this error?

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

Any ideas why this would do this on the new server and NOT on the old one? No code has changed and the DB is a backup of the one from the old server??

Answer 1

Ah, I would say you have a culture issue. Probably you need / wait for date and string formats in a specific form, and the coding in the current process is different ;) YOu probably adid adjust the culture in the old server, and did not on the new one.

Answer 2

From the error message, whats probably happening here is that the ASP app is sending date strings to SQL server, e.g. it is building queries with Where clauses like this:

WHERE SomeDateField > '20/04/2010'

... and some different regional settings on the new platform are preventing SQL from parsing the date-string. Usually, this comes down to the US vs UK date format thing of putting the month in a different place. Either:

• The regional settings in Windows are different, causing ASP to build the date-string in a different way, or
• The regional settings in SQL are different, causing SQL to attempt to parse the date-string in a different way.

From the conversation we had below, it looks like it might be the regional settings in SQL.

In SQL Server Standard, there are generally the following language settings that can affect how date-strings are parsed:

• An overall Language setting for the Server
• A language setting for each Login defined on the server (look at the Properties dialog for the login)

I'm pretty sure SQL Express will also have the same settings. Fire up "Microsoft SQL Server Management Studio Express" on both servers and compare the Server and Login language settings.