0

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

skaffman
  • 398,947
  • 96
  • 818
  • 769
shoab
  • 545
  • 3
  • 7
  • 19

2 Answers2

3

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
2

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)

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • the dashed date format you've shown isn't safe if language is British. It would be with a `T` placed between the date and time (instead of the space) – Damien_The_Unbeliever May 12 '11 at 12:12
  • have changes to date format on live server os to MM/dd/yyyy but still getting same error – shoab May 16 '11 at 12:39
  • @shoab - you need also to check that the account(s) under which the connection is made to SQL server. You could also try setting the date format on the connection (before issuing any commands) - `SET DATEFORMAT mdy` – Ed Harper May 16 '11 at 14:04