Why Windows Server 2003 causes "varchar to datetime out-of-range", wheras Server 2008 does not

I have written a small Asp.net application using Entity Framework.

A Stored Procedure accepts the following: employeeID int, startDate varchar(12),endDate varchar(12)

I then use SQL server to convert the short date strings to DateTime.

SET @correctStartDate = CONVERT(DATETIME,(convert(varchar(10),@startDate,103) + ' 00:00:00am'), 103)
SET @correctEndDate = CONVERT(DATETIME,(convert(varchar(10),@endDate,103) + ' 11:59:59pm'), 103)

In development environment iis7 Windows Server 2008 with SQL Server 2008 R2 there are no issues.

If we deploy to production server iis6 (Windows Server 2003 SP2 with SQL Server 2008 R2) we get:

"The conversion of a varchar data type to a datetime data type resulted i开发者_运维技巧n an out-of-range value.The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Why would this be? Both have regional settings the same.

What are the values of start and end date?

Try doing the same using datetime2 instead of datetime.

The problem was what Martin said. The language settings have a login context.

To solve the issue I have inserted the datetime into the stored procedure instead of the datetime.ToShortDateString() method.

You could also use myDatetime.ToString("dd/MM/yyyy");