how to compare only date components from datetime In Entity framework?

using (dysoft.Data.CAccessDataClass oData = new dysoft.Data.CAccessDataClass())
            {
                using (HW_MangerDataEntities ctx = new HW_MangerDataEntities(IoriBaseDataAccess.getEntityConnByIDBConnection(oData.sqlConn, "CustomerPlatform")))
                {
                    var linq = from row in ctx.tHWFinances
                               where row.iState == 2 && row.iType == 1//申核通过的, 客户预付款
                               select row;
                    if (string.IsNullOrEmpty(condition.clientCode) == false)
                    {
                        linq = linq.Where(row => row.cClientCode == condition.clientCode);
                    }
                    if (string.IsNullOrEmpty(condition.memo) == false)
                    {
                        linq = linq.Where(row => row.cMemo.Contains(condition.memo));
                    }
                    if (condition.prePayEnum > 0)
                    {
                        linq = linq.Where(row => row.iType == (int)condition.prePayEnum);
                    }

if (condition.dStart != null)
{
　　//linq = linq.Where(row => row.dDate >= condition.dStart);
　　//linq = linq.Where(row => System.Data.Linq.SqlClient.SqlMethods.DateDiffDay(row.dDate, (DateTime)condition.dStart) >= 0);
　　//linq = linq.Where(row => System.Data.Objects.SqlClient.SqlFunctions.DateDiff("d",row.dDate, (DateTime)condition.dStart) >= 0);
　　linq = linq.Where(row => System.Data.Objects.EntityFunctions.DiffDays((DateTime)condition.dStart, row.dDate) >= 0);
}
if (condition.dEnd != null)
{
　　//linq = linq.Where(row => DateTime.Compare(row.dDate, (DateTime)condition.dEnd) <= 0);
　　linq = linq.Where(row => System.Data.Objects.SqlClient.SqlFunctions.DateDiff("d", (DateTime)condition.dEnd, row.dDate) <= 0);
}

                    result = linq.ToList();
                }
            }

exec sp_executesql N'SELECT 
[Extent1].[iID] AS [iID], 
[Extent1].[cSupplierCode] AS [cSupplierCode], 
[Extent1].[cClientCode] AS [cClientCode], 
[Extent1].[cDate] AS [cDate], 
[Extent1].[dDate] AS [dDate], 
[Extent1].[iState] AS [iState], 
[Extent1].[iType] AS [iType], 
[Extent1].[cMemo] AS [cMemo], 
[Extent1].[cProject] AS [cProject], 
[Extent1].[nMoney] AS [nMoney], 
[Extent1].[cAuditor] AS [cAuditor], 
[Extent1].[dAuditor] AS [dAuditor], 
[Extent1].[iFrom] AS [iFrom], 
[Extent1].[cCardID] AS [cCardID], 
[Extent1].[dCreate] AS [dCreate], 
[Extent1].[cOperator] AS [cOperator], 
[Extent1].[cCoCode] AS [cCoCode]
FROM [dbo].[tHWFinance] AS [Extent1]
WHERE (2 =  CAST( [Extent1].[iState] AS int)) 
AND (1 = [Extent1].[iType]) 
AND ([Extent1].[cClientCode] = @p__linq__0) 
AND ( CAST( [Extent1].[dDate] AS datetime2) >= @p__linq__1) 
AND ([Extent1].[dDate] <= @p__linq__2)',
N'@p__linq__0 varchar(8000),
@p__linq__1 datetime2(7),
@p__linq__2 datetime2(7)',
@p__linq__0='01',
@p__linq__1='2017-07-05 00:00:00',
@p__linq__2='2017-07-29 00:00:00'

 

up vote104down votefavorite

9

I am having two date values, one already stored in the database and the other selected by the user using DatePicker. The use case is to search for a particular date from the database.

The value previously entered in the database always has time component of 12:00:00, where as the date entered from picker has different time component.

I am interested in only the date components and would like to ignore the time component.

What are the ways to do this comparison in C#?

Also, how to do this in LINQ?

UPDATE: On LINQ to Entities, the following works fine.

e => DateTime.Compare(e.FirstDate.Value, SecondDate) >= 0

c# linq entity-framework linq-to-entities datetime-comparison

shareimprove this question

edited May 20 '12 at 18:33 abatishchev 61.8k62231370

asked Sep 25 '09 at 16:07 pencilslate 5,341154369

1

You can also take a look at this SO question: stackoverflow.com/questions/683037/how-to-compare-dates-in-c‌​/… – Quintin Robinson Sep 25 '09 at 16:16

add a comment

15 Answers

activeoldestvotes

up vote116down voteaccepted

NOTE: at the time of writing this answer, the EF-relation was unclear (that was edited into the question after this was written). For correct approach with EF, check Mandeeps answer.

---

You can use the DateTime.Date property to perform a date-only comparison.

DateTime a = GetFirstDate();
DateTime b = GetSecondDate();

if (a.Date.Equals(b.Date))
{
    // the dates are equal
}

shareimprove this answer

edited May 23 at 12:34 Community♦ 11

answered Sep 25 '09 at 16:10 Fredrik Mörk 118k18224297

29

It's easy to compare date but the question is related to LINQ to Entities who is unable to convert .Date property into SQL. – Michaël Carpentier Jan 30 '13 at 13:06

@MichaëlCarpentier: good point. Apparently it still solved the OP's problem. – Fredrik Mörk Jan 30 '13 at 13:17

5

This doesn't query the database but rather processes the data in the CLR / application layer after the fact. The real solution is to use the EntityFunctions.TruncateTime(..) function as specified in the answer below, since it sends the query to the database and allows the processing to be done at the storage layer. Without this you couldn't use the date comparison logic in Where / Count clauses and then further query on the filtered data, since you'd have to pull partial results into the application layer first, which can be a deal-breaker in scenarios that process large bodies of data. – Marchy Oct 3 '13 at 2:46 

6

@Marchy Yes, EntityFunctions.TruncateTime certainly seem to be the way to go these days (it became available in .NET 4 which was released the year after this question was asked). – Fredrik Mörk Oct 3 '13 at 7:30

1

use System.Data.Entity.DbFunctions.TruncateTime() method. You need to add a reference to EntityFramework – adeel41 Jun 18 '15 at 10:19 

add a comment

up vote112down vote

Use the class EntityFunctions for trimming the time portion.

using System.Data.Objects;    

var bla = (from log in context.Contacts
           where EntityFunctions.TruncateTime(log.ModifiedDate) ==  EntityFunctions.TruncateTime(today.Date)
           select log).FirstOrDefault();

Source: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/

UPDATE

As of EF 6.0 and later EntityFunctions is replaced by DbFunctions.

shareimprove this answer

edited Sep 28 '16 at 6:03 janmvtrinidad 911620

answered Mar 9 '12 at 23:20 Mandeep Janjua 5,88231820

31

Just a note EntityFunctions has been deprecated in favor of System.Data.Entity.DbFunctions for (at least) EF6. It may have been earlier than this. – pquest Sep 2 '14 at 19:55

3

I wouldn't be quick to jump to this solution as it is really slow, more info:stackoverflow.com/questions/22776843/… – pajics Jul 22 '15 at 9:05

This doesn't work when backed by MySql? – sheamus Nov 3 '16 at 18:08

add a comment

up vote21down vote

I think this could help you.

I made an extension since I have to compare dates in repositories filled with EF data and so .Date was not an option since it is not implemented in LinqToEntities translation.

Here is the code:

        /// <summary>
    /// Check if two dates are same
    /// </summary>
    /// <typeparam name="TElement">Type</typeparam>
    /// <param name="valueSelector">date field</param>
    /// <param name="value">date compared</param>
    /// <returns>bool</returns>
    public Expression<Func<TElement, bool>> IsSameDate<TElement>(Expression<Func<TElement, DateTime>> valueSelector, DateTime value)
    {
        ParameterExpression p = valueSelector.Parameters.Single();

        var antes = Expression.GreaterThanOrEqual(valueSelector.Body, Expression.Constant(value.Date, typeof(DateTime)));

        var despues = Expression.LessThan(valueSelector.Body, Expression.Constant(value.AddDays(1).Date, typeof(DateTime)));

        Expression body = Expression.And(antes, despues);

        return Expression.Lambda<Func<TElement, bool>>(body, p);
    }

then you can use it in this way.

 var today = DateTime.Now;
 var todayPosts = from t in turnos.Where(IsSameDate<Turno>(t => t.MyDate, today))
                                      select t);

shareimprove this answer

answered Nov 18 '09 at 23:59

community wiki jrojo

add a comment

up vote9down vote

If you use the Date property for DB Entities you will get exception:

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

You can use something like this:

  DateTime date = DateTime.Now.Date;

  var result = from client in context.clients
               where client.BirthDate >= date
                     && client.BirthDate < date.AddDays(1)
               select client;

shareimprove this answer

answered Jul 26 '12 at 8:48 algreat 5,31342948

add a comment

up vote8down vote

To do it in LINQ to Entities, you have to use supported methods:

var year = someDate.Year;
var month = ...
var q = from r in Context.Records
        where Microsoft.VisualBasic.DateAndTime.Year(r.SomeDate) == year 
              && // month and day

Ugly, but it works, and it's done on the DB server.

shareimprove this answer

answered Sep 30 '09 at 16:46 Craig Stuntz 113k9219256

add a comment

up vote8down vote

Here's a different way to do it, but it's only useful if SecondDate is a variable you're passing in:

DateTime startDate = SecondDate.Date;
DateTime endDate = startDate.AddDays(1).AddTicks(-1);
...
e => e.FirstDate.Value >= startDate && e.FirstDate.Value <= endDate

I think that should work

shareimprove this answer

answered Jul 1 '11 at 17:12 John Kaster 1,4642027

1

Excellent. Worked for me. It was the explicit DateTime = x.Date; I was missing. If I used var, or had the value inline in the comparison it failed with the exception reported. Thanks. – Tim Croydon Jun 1 '12 at 9:45

Glad it worked, Tim. Sorry for the delay in responding - I haven't actually logged in to SO in a while. – John Kaster Oct 22 '12 at 0:05

1

If you change e.FirstDate.Value <= endDate to e.FirstDate.Value < endDate you can remove the.AddTicks(-1). – Marco de Zeeuw Aug 9 '15 at 12:40

@MarcodeZeeuw you're right, that would definitely work as well. The conditional expression shown is intended for inclusive date comparisons of exact start and end datetimes (assuming the date range values would be passed in to the condition rather than set up in a code fragment.) IOW, the conditional is considered separate from the datetime values. – John Kaster Aug 12 '15 at 0:05

add a comment

up vote5down vote

You can also use this:

DbFunctions.DiffDays(date1, date2) == 0

shareimprove this answer

answered Dec 17 '15 at 16:46 user3829854 12514

add a comment

up vote4down vote

Just always compare the Date property of DateTime, instead of the full date time.

When you make your LINQ query, use date.Date in the query, ie:

var results = from c in collection
              where c.Date == myDateTime.Date
              select c;

shareimprove this answer

answered Sep 25 '09 at 16:10 Reed Copsey 424k438611176

9

I am getting the error "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.". Any thoughts? – pencilslate Sep 25 '09 at 16:46

Yeah - your provider doesn't handle the .Date property directly. You'll have to pull it out, and compare the dates later. – Reed Copsey Sep 25 '09 at 17:03

.Date can't be used in Linq To Entities, unfortunately. Hopefully MS will add that overload support soon – John Kaster Jul 1 '11 at 16:59

1

Always compare the Date property? I've googled into this comment because I have wondered if that is the best practice, ie. to always use the Date property, even when it's something like candidate.Date >= base.Date. Theoritically, the candidate.Date time must be >= 12:00:00, so using the Date property is redundant, but I'll stick with Reed's advice. – Stephen Hosking Apr 11 '12 at 6:12 

add a comment

up vote3down vote

Just if somebody arrives here googling or binging... compiled workarounds:

http://blog.integratedsolution.eu/post/2011/02/06/The-specified-type-member-Date-is-not-supported-in-LINQ-to-Entities.aspx

shareimprove this answer

answered Jul 29 '11 at 21:49 Braulio 1,4061213

add a comment

up vote3down vote

This is how I do this.

DateTime date_time_to_compare = DateTime.Now;
//Compare only date parts
context.YourObject.FirstOrDefault(r =>
                EntityFunctions.TruncateTime(r.date) == EntityFunctions.TruncateTime(date_to_compare));

shareimprove this answer

answered Feb 17 '14 at 9:25 Alejandro del Río 1,82812020

add a comment

up vote2down vote

//Note for Linq Users/Coders

This should give you the exact comparison for checking if a date falls within range when working with input from a user - date picker for example:

((DateTime)ri.RequestX.DateSatisfied).Date >= startdate.Date &&
        ((DateTime)ri.RequestX.DateSatisfied).Date <= enddate.Date

where startdate and enddate are values from a date picker.

shareimprove this answer

edited Aug 5 '11 at 12:17 Alberto Zaccagni 19.7k55089

answered Aug 4 '11 at 16:01 Leo Di Salty 212

add a comment

up vote1down vote

You can user below link to compare 2 dates without time :

private bool DateGreaterOrEqual(DateTime dt1, DateTime dt2)
        {
            return DateTime.Compare(dt1.Date, dt2.Date) >= 0;
        }

private bool DateLessOrEqual(DateTime dt1, DateTime dt2)
        {
            return DateTime.Compare(dt1.Date, dt2.Date) <= 0;
        }

the Compare function return 3 different values: -1 0 1 which means dt1>dt2, dt1=dt2, dt1

shareimprove this answer

edited Apr 18 '12 at 9:44 antyrat 22k84869

answered Apr 18 '12 at 9:36 majid 191

Why don't you just return DateTime.Compare(dt1.Date, dt2.Date)? This makes all you need. – Johnny GraberOct 27 '12 at 6:58

add a comment

up vote1down vote

you can use DbFunctions.TruncateTime() method for this.

e => DbFunctions.TruncateTime(e.FirstDate.Value) == DbFunctions.TruncateTime(SecondDate);

shareimprove this answer

answered Nov 11 '16 at 6:26 Harsh Vyas 6212

add a comment

up vote0down vote

Without time than try like this:

TimeSpan ts = new TimeSpan(23, 59, 59);
toDate = toDate.Add(ts);
List<AuditLog> resultLogs = 
    _dbContext.AuditLogs
    .Where(al => al.Log_Date >= fromDate && al.Log_Date <= toDate)
    .ToList();
return resultLogs;

shareimprove this answer

edited Feb 20 '12 at 1:38 Dmitry Lobanov 3,11412445

answered Feb 18 '12 at 6:55 Nalan M 3,5322218

add a comment

up vote0down vote

Try this... It works fine to compare Date properties between two DateTimes type:

PS. It is a stopgap solution and a really bad practice, should never be used when you know that the database can bring thousands of records...

query = query.ToList()
             .Where(x => x.FirstDate.Date == SecondDate.Date)
             .AsQueryable();

shareimprove this answer

edited Sep 15 '15 at 15:01

answered Feb 6 '12 at 16:58 Raskunho 92

1

P.S.: I usually use this way when the DateTimes have Time value and I want to compare only the Date. – Raskunho Feb 19 '12 at 0:36

2

this is a very bad solution, the query will get all the records, and only then filter out the dates. if the database has millions of records, this will grab all of them and only then will filter the dates. VERY BAD PRACTICE. – Dementic May 27 '15 at 7:57

1

It is a stopgap solution and a really bad practice, should never be used when you know that the database can bring thousands of records. – Raskunho Aug 13 '15 at 21:07

if you'll add your comment into your answer, i will remove my down-vote. it should be clear to anyone visiting this page that the solution you proposed is bad without having to read the comments. – Dementic Aug 14 '15 at 7:44

@Dementic, the answer was edited. – Raskunho Sep 15 '15 at 15:03