0

I am in progress of migrating some databases from SQL Server 2008 to SQL Server 2012. I have just moved a database that a web application points to and the page is erroring.

The asp.net page has a SQL statement within it. It's selecting based on 2 dates entered into 2 text boxes (I know it should be a stored procedure but it isn't one of my sites). When I point the website to the SQL Server 2008 database, it works fine but when I change the connection string to the 2012 box it fails stating it cannot convert the date.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Silentbob
  • 2,805
  • 7
  • 38
  • 70
  • 1
    Database settings are kind of ruled out if you restored the database. Is the OS on both servers set to the same locale? – Andomar Dec 16 '13 at 13:47
  • What is the type of your `Date` column is it `DateTime` or `Date` ? Also I suspect that values from ASP.Net are passed as string not DateTime via parameters. – Habib Dec 16 '13 at 13:47
  • IS the web server the same? How are you parsing the data? Are you using parameters is building a SQL string? – D Stanley Dec 16 '13 at 13:48
  • The server is set as the same locale, its a Date column and the website declares a startdate parameter and enddate – Silentbob Dec 16 '13 at 13:49
  • 1
    Show us the code how you're selecting the data! Most likely, your two SQL Server instances have two different language/regional settings, and the dates are being specified as a string, but **not** using the language-agnostic ISO-8601 format (`YYYYMMDD` or `YYYY-MM-DDTHH:MM:SS`) only --> thus your query is dependent on the language settings on each box.... – marc_s Dec 16 '13 at 13:59
  • 3
    The default language of the logins between the two instances are likely not set to the same thing. If you use parameterised queries of the correct datatype then you won't get this type of issue. – Martin Smith Dec 16 '13 at 14:00
  • Martin Smith has a good point, logins are stored in the master database, so their settings are not copied by backup/restore. – Andomar Dec 16 '13 at 14:02
  • Spot on Martin, thanks would not of thought to look there. – Silentbob Dec 16 '13 at 14:10

0 Answers0