Comparing Dates in Linq To SQL

If you want to compare dates in Linq to SQL query, you are free to use normal operators available in C# and Linq to SQL provider will translate the date comparison into appropriate SQL which makes uses of date functions available on SQL server. To demonstrate the usage of Date functions let's look at an example.

In the above example, I am getting an instance of Date Time object by parsing date from a string. I have an event table which has an event date that defines when the event will happen. To find an event that matches the date we have selected, I am comparing my date in the lambda expression to the date available on the Event Timing table. Linq to SQL is smart enough to convert the expression into appropriate SQL that SQL server can execute. Above query generates the follow SQL query to be executed by the database.

Above SQL query is the translation of our comparison of dates in Linq query. From the query, we can assert that this comparison was by no means complicated. It simply compares two fields ensuring that the parameter passed in to the SQL query is of Datetime data type.

But sometimes we don't really care about the time portion of the date and all we care is about finding an item in the database that matches a specific date with no time. Well, if you are using SQL server 2008, than I would highly recommend that you make use of the new data type called date which only captures the date portion of a date time. If SQL server 2008 is not an option, you can do your regular comparison of just the date portion in your query and Linq to SQL provider would gladly convert your query into appropriate SQL translation that only compares the date portion of a date. Below is an example of a query where I am only comparing date portions of the query.

In the above example, I make use of Date property available on a Date instance to just get the date portion of my selection date. To ensure that both sides of query make use of just the date portion, in my Linq query, I am getting just the date portion from my event date and comparing that to my selected date. When I run the above query, I get the following SQL query.

In the above SQL query, we are just grabbing the date portion from EventDate column by making use of convert operator passing in date as one of parameter. This confirms that when we make use of Date property on the Datetime object as part of our Linq query, Linq provider will translate the query which will make use of Convert function to convert Datetime instance to just date. It might be surprising that value portion of our date still consists of date and time. This is because  Date property on Datetime object return the date and a default time and since the convert function in SQL server also returns a date with default time, the criteria matches based on dates with default time.