am passing default dates (startdate & enddate) to an sp as ('1/1/1753 12:00:00 AM' & '12/31/9999 12:00:00 AM').
This was working fine in 2005, but am getting "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" exception in 2008.
And the twist is such that when i run the sp from Management studio it works fine but on the live server through website it gives error.
thanks in advance
I'd agree with Ed Harper that the problem is likely to be locale or language setting differences between different environments. I'd disagree with his fix.
Get those datetime values into DateTime variables, as soon as possible, and don't treat them as strings. I.e. do that on the website, and then rely on e.g. ADO.Net to correctly marshal datetime values across to SQL Server. Update the stored proc to expect the parameters as datetimes also - basically, stop treating them as strings as early as possible in your processing.
This is likely to be a problem with the locale in which the OS was installed or the locales linked to user accounts under which the code is running, because different locales interpret dates in the format you are supplying as either DD/MM/YYYY or MM/DD/YYYY
The best way to get around the issue is to use a canonical date format - either YYYY-MM-DDTHH:MM:SS AM/PM, YYYYMMDD HH:MM:SS AM/PM or YYYY-MM-DD HH:MM:SS(24h)
