Why Must I Have "SET DATEFORMAT dmy" in Stored Proc?

[Guest]

I'm calling a stored proc from C# and the call includes a datetime. The exe
can run on machine A or B but the database is on machine C. When I run on
machine A I need SET DATEFORMAT dmy in the proc or I get an 'out-of-range
datetime value' message. I need to comment this statement out when the call
is made from machine B.

It seems as though I have a UK locale on machine A and a US locale on
machine B and the swapping of days and months causes the error. Is this
locale setting per machine possible? If so then how to change it?

TIA, jeb.

 

[Patrice]

How do you pass the value ?

It looks like that the datetime is passed as text which is sensitive to
local settings (DD/MM/YYYY or MM/DD/YYYY depending on the country)
explaining why you have to make the server match this format.

You could use the ISO format (YYYYMMDD) that always works regardless of
settings or pass the parameter as a real datetime value rather than text
(using a parameter).