Click here to Skip to main content
Click here to Skip to main content
Go to top

LINQ query to compare only date part of DateTime

, 29 Nov 2012
Rate this:
Please Sign up or sign in to vote.
LINQ query to compare only date part of DateTime.


In this post I am going to discuss about comparing the date part of datetime in a LINQ query. In a LINQ query it is easy to perform datetime comparisons, to compare only date not time part of a datetime field of your entity.

Let us have a coloser look. In SQL Server we can do something like below to just compare the date part of the field.
SELECT * FROM dbo.tbl_MyTable
WHERE 
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) = 
            CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)
So in the above query convert function does the task of removing the time part and only the date part comparison happens.

LINQ Quries
Following discussion is about doing the same task using LINQ queries.
Solution 1:
The first way to achieve the same thing (i.e., comparing date part) of an entity or object is following:
var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                  && DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                  .ToList();
Here Date property of DatTime is used to get only the date part of the datetime property and makes use of the DateTime.Compare function to get the matching object.

But the problem with this approach is when make use of EnityFramework, i.e., LINQ to Entity, it gives following error at runtime:

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

To avoid the above error in LINQ to Entity, the query is modified to following:
var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong")
                            .ToList()
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                            .ToList(); 

So in the above query first data is fetched from the database and then on list, date comparison get applied. But the problem with this approach is we need to load all data first then the date comparison gets applied because Entity Framework doesn't support a direct query.

Solution 2:
One more easy and simple solution to just compare the date part of a datetime object as follows:
            
var data1 = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                            && x.price_date.Value.Year == dt.Year
                            && x.price_date.Value.Month == dt.Month
                            && x.price_date.Value.Day == dt.Day).ToList();
The query uses year, month, and day properties of the datetime object to compare dates. The advantage of this solution is this is compatible with all flavors of LINQ, i.e., it works in LINQ to SQL, LINQ to object, and also in LINQ to Enitity.

Conclusion
So the post is useful when you need to compare only the date part of the datetime property in LINQ queries.

Leave your comments if you like it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)

 
Hey, I am Pranay Rana, working as a ITA in MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.
 
For me def. of programming is : Programming is something that you do once and that get used by multiple for many years
 

You can visit my blog

StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr
 
Awards:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionWith EF 6 there is another way Pinmemberrobvon20-Sep-14 9:41 
GeneralMy vote of 4 PinmemberOscarw29-Aug-14 10:41 
GeneralMy vote of 5 Pinmembergicalle7520-Feb-14 22:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 29 Nov 2012
Article Copyright 2012 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid