Weird SQL Server stored procedure behaviour - different results between asp.net and Management Studio <p>We have a stored procedure that’s used to populate a SSRS Report. It’s a bit of a monster – loads of conditional logic (in case statements), casts (sometimes embedded in other casts) from varchar to datetime, and 3 fields that rely on calls to a function that contains some date functions . The report contains financial information for a list of contracts for one organisation.</p> <p>I need to create an asp.net page that shows a subset of the data for one organisation/contract so I cloned the sproc and added a contract parameter. However I noticed the figures for the 3 fields that rely on the function are different on the web page from when the stored procedure is run directly on the database or through the report.</p> <p>To troubleshoot I created a page (quick and dirty using <code>SqlDataSource</code> and <code>DataGrid</code>) that shows the results of the original stored procedure showing all contracts. The query runs fine through Enterprise Manager but the web page crashes with the YSOD and the message </p> <blockquote> <p>The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.</p> </blockquote> <p>I even tried hardcoding the SQL from the stored procedure into the web page but still get the same results</p> <p>On my dev machine the original stored procedure runs and my new stored procedure does return consistent results regardless of whether viewed through the web page or Management Studio. The regional settings etc are same on the dev server and live server. The only different thing I can think of is that the live web server and db server are on separate machines.</p> <p>Has anyone come across anything like this before??</p> <p>Thanks</p>