Problem with Linq query and date format

Question

I have a C# console application written using Visual Studio 2008.

My system culture is en-GB. I have a Linq query that looks like this:

var myDate = "19-May-2010";

var cus = from x in _dataContext.testTable
     where x.CreateDate == Convert.ToDateTime(myDate)
     select x;

The resulting SQL query generates and error because it returns the dates as "19/05/2010" which it interprets as an incorrect date. For some reason even though my system culture is set to en-GB it looks like it's trying to intrepret it as a en-US date.

Any ideas how I get around this?

Edit: Thanks for the comments about magic strings and var abuse, but that's not my problem. My problem is that in the conversion from Linq to SQL the dates are being interpreted as US format dates (19/05/2010 is being interpreted as: month nineteen, day 5 and year 2010) resulting in the following error:

System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

The where clause of the resulting SQL query looks like:

WHERE ([t0].[CreateDate] = '19/05/2010 00:00:00') 

Please note that the exact same Linq query works perfectly in LinqPad.

I've tried the following where clause:

where x.CreateDate == DateTime.Today

and still get the error.

Additional Information:

SQL Server Query Visualizer:

SELECT [t0].[field1], [t0].[field2], [t0].[field3] AS [field4], [t0].[field5]
FROM [dbo].[table] AS [t0]
WHERE ([t0].[CreateDateTime] = '19/05/2010 00:00:00') 

Original query:
SELECT [t0].[field1], [t0].[field2], [t0].[field3] AS [field4], [t0].[field5]
FROM [dbo].[table] AS [t0]
WHERE ([t0].[CreateDateTime] = @p0) 
-------------------------------
@p0 [DateTime]: 19/05/2010 00:00:00

---

LINQPad:

-- Region Parameters
DECLARE @p0 DateTime SET @p0 = '2010-05-19 00:00:00.000'
-- EndRegion
SELECT [t0].[field1], [t0].[field2], [t0].[field3] AS [field4], [t0].[field5]
FROM [table] AS [t0]
WHERE ([t0].[CreateDateTime] = @p0)

In the above I notice that LinqPad presents the date in a different format to VS.

Thanks.

Alan T

Answer 1

Don't send local strings into the database and ask the database to convert those strings into DateTimes. Just don't.

Originally:

var myDate = "19-May-2010"; 

var cus = from x in _dataContext.testTable 
  where x.CreateDate == Convert.ToDateTime(myDate) 
  select x; 

• Don't abuse var.
• Separate actions you intend to occur in the database from actions you intend to occur locally

So...

string myInput = "19-May-2010";
DateTime myDate = Convert.ToDateTime(myInput);

IQueryable<TestTable> cus =
  from x in _dataContext.testTable
  where x.CreateDate == myDate
  select x;

---

In response to the update.

• It seems that your app is sending the rightly formatted date times, but it's using a connection that expects wrongly formatted datetimes.
• You can change each connection or you can change the login's defaults: http://support.microsoft.com/kb/173907

Answer 2

I know you probably checked this, but can you verify the following:

1. CreateDate is declared as a DateTime or DateTime2 in the database
2. The CreateDate property in your Linqed class is declared as a .NET DateTime
3. The System.Linq.Mapping.ColumnAttribute is defined properly on your CreateDate property in your Linqed up class.

Based on the fact that you're seeing success with the query in LinqPad, I suspect that there's something wrong with the CreateDate property or the SQL definition of that field. I'm REALLY suspicious that your Linq-to-SQL output says "WHERE ([t0].[CreateDate] = '19/05/2010 00:00:00')" instead of "WHERE ([t0].[CreateDate] = @p0)".

Answer 3

What happens when you try this

var myDate = "20100519"; 

var cus = from x in _dataContext.testTable 
     where x.CreateDate == Convert.ToDateTime(myDate) 
     select x; 

Answer 4

As Bill said, don't use strings to represent dates. LINQ to SQL uses parameterized queries to pass parameters so you shouldn't have any problems with locales - provided that your database field and your parameter are both dates.

You can check the generated SQL statement by attaching a TextWriter objec (like Console.Out) to the DataContext's Log property.The following code

using(var datacontext=new DatesDataContext())
{
    var myDate=DateTime.Today;
    //Or, to specify a date without string parsing
    //var myDate=new DateTime(2010,6,16);
    var dates = from date in datacontext.DateTables
        where date.DateField == myDate                 
        select date;

    datacontext.Log = Console.Out;
    foreach (var date in dates)
    {
        Console.WriteLine(date.DateField);
    }
}

produced this query

SELECT [t0].[DateField]
FROM [dbo].[DateTable] AS [t0]
WHERE [t0].[DateField] = @p0
-- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [6/16/2010 12:00:00 AM]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

As you see, the query has a parameter of type DateTime and returned the proper entries.

What exactly is the error you encounter? Do you get an exception or do you get incorrect results? If you get no results perhaps your database field contains a time value, in which case it won't match a parameter that contains only a date value

Answer 5

You can use:

x.CreateDate == DateTime.ParseExact(myDate, "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture);

This assumes, of course, that your date in the DB matches your myDate field (i.e. if you have time included with the date, you will need to modify the above to include the time portion).

Answer 6

Short answer is "don't use strings to represent dates".

Assuming the CreateDate column is a SQL Server datetime, you should be able to just do your compare with a .NET DateTime class.