CODEWITHSUNDEEP
c#entity-frameworkdatetimelinq-to-entitiescompare
Frank Myat Thu
Frank Myat Thu

Reputation: 4474

Linq where clause compare only date value without time value

var _My_ResetSet_Array = _DB
    .tbl_MyTable
    .Where(x => x.Active == true
        && x.DateTimeValueColumn <= DateTime.Now)
    .Select(x => x);

Upper query is working correct.
But I want to check only date value only.
But upper query check date + time value.

In traditional mssql, I could write query like below.

SELECT * FROM dbo.tbl_MyTable
WHERE 
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) <= 
            CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)
AND
Active = 1

So could anyone give me suggestion how could I check only date value in Linq.

Upvotes: 65

Views: 152896

Answers (10)

Johann Blais
Johann Blais

Reputation: 9469

There is also EntityFunctions.TruncateTime or DbFunctions.TruncateTime in EF 6.0 or later

Upvotes: 142

Roberto Mutti
Roberto Mutti

Reputation: 95

Do not simplify the code to avoid "linq translation error": The test consist between a date with time at 0:0:0 and the same date with time at 23:59:59

        iFilter.MyDate1 = DateTime.Today;  // or DateTime.MinValue

        // GET 
        var tempQuery = ctx.MyTable.AsQueryable();

        if (iFilter.MyDate1 != DateTime.MinValue)
        {
            TimeSpan temp24h = new TimeSpan(23,59,59);
            DateTime tempEndMyDate1 = iFilter.MyDate1.Add(temp24h);

            // DO not change the code below, you need 2 date variables...
            tempQuery = tempQuery.Where(w => w.MyDate2 >= iFilter.MyDate1
                                          && w.MyDate2 <= tempEndMyDate1);
        }

        List<MyTable> returnObject = tempQuery.ToList();

Upvotes: 0

Karthic G
Karthic G

Reputation: 1182

Try this,

var _My_ResetSet_Array = _DB
    .tbl_MyTable
    .Where(x => x.Active == true
         && x.DateTimeValueColumn <= DateTime.Now)
    .Select(x => x.DateTimeValueColumn)
    .AsEnumerable()
    .select(p=>p.DateTimeValueColumn.value.toString("YYYY-MMM-dd");

Upvotes: 0

Soharab Shaikh
Soharab Shaikh

Reputation: 3

Working code :

     {
        DataBaseEntity db = new DataBaseEntity (); //This is EF entity
        string dateCheck="5/21/2018";
        var list= db.tbl
        .where(x=>(x.DOE.Value.Month
              +"/"+x.DOE.Value.Day
              +"/"+x.DOE.Value.Year)
             .ToString()
             .Contains(dateCheck))
     }

Upvotes: 0

Jatin
Jatin

Reputation: 29

 result = from r in result where (r.Reserchflag == true && 
    (r.ResearchDate.Value.Date >= FromDate.Date && 
     r.ResearchDate.Value.Date <= ToDate.Date)) select r;

Upvotes: 2

takemyoxygen
takemyoxygen

Reputation: 4394

In similar case I used the following code:

DateTime upperBound = DateTime.Today.AddDays(1); // If today is October 9, then upperBound is set to 2012-10-10 00:00:00
return var _My_ResetSet_Array = _DB
    .tbl_MyTable
    .Where(x => x.Active == true
        && x.DateTimeValueColumn < upperBound) // Accepts all dates earlier than October 10, time of day doesn't matter here
    .Select(x => x);

Upvotes: 1

Joe Stellato
Joe Stellato

Reputation: 568

&& x.DateTimeValueColumn <= DateTime.Now

This is supported so long as your schema is correct

&& x.DateTimeValueColumn.Value.Date <=DateTime.Now

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176956

EDIT

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

var _My_ResetSet_Array = _DB
                .tbl_MyTable
                .Where(x => x.Active == true)
                         .Select(x => x).ToList();

 var filterdata = _My_ResetSet_Array
        .Where(x=>DateTime.Compare(x.DateTimeValueColumn.Date, DateTime.Now.Date)  <= 0 );

The second line is required because LINQ to Entity is not able to convert date property to sql query. So its better to first fetch the data and then apply the date filter.

EDIT

If you just want to compare the date value of the date time than make use of

DateTime.Date Property - Gets the date component of this instance.

Code for you 

var _My_ResetSet_Array = _DB
                .tbl_MyTable
                .Where(x => x.Active == true
     && DateTime.Compare(x.DateTimeValueColumn.Date, DateTime.Now.Date)  <= 0 )
                         .Select(x => x);

If its like that then use

DateTime.Compare Method - Compares two instances of DateTime and returns an integer that indicates whether the first instance is earlier than, the same as, or later than the second instance.

Code for you 

var _My_ResetSet_Array = _DB
                .tbl_MyTable
                .Where(x => x.Active == true
                  && DateTime.Compare(x.DateTimeValueColumn, DateTime.Now)  <= 0 )
                         .Select(x => x);

Example 

DateTime date1 = new DateTime(2009, 8, 1, 0, 0, 0);
DateTime date2 = new DateTime(2009, 8, 1, 12, 0, 0);
int result = DateTime.Compare(date1, date2);
string relationship;

if (result < 0)
   relationship = "is earlier than";
else if (result == 0)
   relationship = "is the same time as";         
else
   relationship = "is later than";

Upvotes: 4

Pranay Rana
Pranay Rana

Reputation: 176956

Simple workaround to this problem to compare date part only

var _My_ResetSet_Array = _DB
                    .tbl_MyTable
                    .Where(x => x.Active == true && 
                               x.DateTimeValueColumn.Year == DateTime.Now.Year
                            && x.DateTimeValueColumn.Month == DateTime.Now.Month
                            && x.DateTimeValueColumn.Day == DateTime.Now.Day);

Because 'Date' datatype is not supported by linq to entity , where as Year, Month and Day are 'int' datatypes and are supported.

Upvotes: 20

igrimpe
igrimpe

Reputation: 1785

Use mydate.Date to work with the date part of the DateTime class only.

Upvotes: -4

Related Questions