Dateformat mismatch in sql server and C# code

Question

I have a problem.

I have an application in C# with SQL SERVER 2005 as backend.

The problem is in fetching the correct record based on the date.

The frontend code is

if (string.IsNullOrEmpty(txtFromDate.Text)) 
    SelectCmd.Parameters[0].Value = DBNull.Value;
else 
    SelectCmd.Parameters[0].Value = txtFromDate.Text;

Now if I run usp_NewGetUserDetail '03/04/2010' in the query analyser, I am able to get the correct record.

So I am preety confident that my SP is correct(I have tested with many variations).

But if the same value is passed from front end code(SelectCmd.Parameters[0].Value = "03/04/2010";), I am getting some unexpected record. By that I mean , the records which are not in the date range.

I guess that there is some mismatch in date format of backend and frontend.

Kindly let me know if I missed out some information that I need to provide for solving this

Please help.

Answer 1

There's no need to pass the date as a string, nor should you. Set the value of the parameters to a DateTime object that represents the date you want, not the string representation of it.

Answer 2

Try something like this:

DateTime fromDate;
if (DateTime.TryParse(txtFromDate.Text, out fromDate))
{
    SelectCmd.Parameters[0].Value = fromDate
}
else
{
    SelectCmd.Parameters[0].Value = DBNull.Value;
}

Answer 3

Dealing with dates on SQL Server is a tricky business, since most formats are language- and locale-dependent. As Adam already mention - you should try to avoid dealing with dates as strings - it does get messy, and using DateTime (both in .NET and in T-SQL) is much safer and easier.

But if you must use strings, then be aware of these points: a date like 02/05/2010 will be interpreted as Feb 5, 2010 in some places, or as May 2, 2010 in others. So whatever you're doing - you'll always run into someone who has a different setting and gets different results.

The way to do here is to use the ISO-8601 format which is independent of all locale and language settings and just always works.

So for your dates, always use the YYYYMMDD format - first of all, it always works, and second of all, with that format, you get a "natural" sort behavior - sorted by Year, then Month, then Day.