Click here to Skip to main content
13,197,200 members (56,291 online)
Click here to Skip to main content
Add your own
alternative version


14 bookmarked
Posted 27 Nov 2012

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
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)
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")
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)

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.

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.


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


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 -
My CV :-


You may also be interested in...

Comments and Discussions

GeneralThanks for sharing Pin
Alireza_136217-Jan-17 9:29
memberAlireza_136217-Jan-17 9:29 
GeneralMy vote of 5 Pin
Yks14-Dec-15 6:18
memberYks14-Dec-15 6:18 
QuestionAlternative is to use Linq CompareTo Pin
martinrj3021-Jun-15 17:51
membermartinrj3021-Jun-15 17:51 
AnswerRe: Alternative is to use Linq CompareTo Pin
Pranay Rana24-Jun-15 20:06
professionalPranay Rana24-Jun-15 20:06 
QuestionUse DbFunctions.TruncateTime() in the Entity Framework version 6. Pin
rama charan3-Jun-15 17:56
memberrama charan3-Jun-15 17:56 
QuestionHow to check weather date is exist or not in database in LINQ to Entity Pin
Ajay Shedge16-Nov-14 17:54
professionalAjay Shedge16-Nov-14 17:54 
QuestionWith EF 6 there is another way Pin
robvon20-Sep-14 9:41
memberrobvon20-Sep-14 9:41 
AnswerRe: With EF 6 there is another way Pin
Member 111471308-Jan-15 5:01
memberMember 111471308-Jan-15 5:01 
GeneralMy vote of 4 Pin
Oscarw29-Aug-14 10:41
memberOscarw29-Aug-14 10:41 
GeneralMy vote of 5 Pin
gicalle7520-Feb-14 22:31
membergicalle7520-Feb-14 22:31 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 29 Nov 2012
Article Copyright 2012 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid