Date comparison format in stored procedure

I have a sql server 2005 database and a stored procedure that accepts two dates and does some comparisons on the data @start_date_from and @start_date_to both of which are of type DATETIME.

We are using LINQ to call the stored procedures from our code and dragging the stored procedure onto the LINQ surface generates a method call which accepts nullable datetimes.

The problem that I am facing is that our dates from the user interface are entered as dd/MM/yyyy (we're in Australia) and parsed as a C# DateTime.

Once the call to the stored proc occurs it seems to convert it to an american style date. I'm looking for a way to prevent this, hopefully without having to change the datatype of the parameters in the proc.

I have tried using SET DATEFORMAT dmy in the proc but it seems to have no affect, I assume it must need a GO or something to take effect.

I was considering maybe trying some funky formatting/conversion/casting once inside the stored proc but that seemed like a last resort.

I guess also I could change the input parameters to varchars and put the date in an unambigious format.

Within the proc itself the dates are using with the BETWEEN 开发者_运维技巧keyword (if that makes any difference).

So what other options (or which of the ones I've outlined) should I go with?

I guess that user input are implicit converted to DateTime? I would say that you need to parse user input to a valid DateTime before calling the SP.

DateTime.Parse("11/12/1981", new CultureInfo("en-US"));

If the culture is different between users you may want to use the current thread's culture:

DateTime.Parse("11/12/1981", System.Threading.Thread.CurrentThread.CurrentUICulture);