Linq where clause compare only date value without time value

Question

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.

score 125 · Accepted Answer · edited Sep 21 '19 at 10:00

125

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

edited Sep 21 '19 at 10:00

Arun Prasad E S

7,342
6
61
75

answered Nov 12 '13 at 17:08

Johann Blais

9,051
6
43
62

2

Replace EntityFunctions with DbFunctions if you an error as EntitiyFunction is deprcated. – Avi Sep 19 '15 at 15:51
5

This worked great. Just a note that you can (actually must) use TruncateTime on both the database column as well as the C# DateTime.Now. Here is the LINQ statement I used: `mydata.Where(t => t.ExpirationDate == null || (t.ExpirationDate != null && DbFunctions.TruncateTime(t.ExpirationDate.Value) > DbFunctions.TruncateTime(DateTime.Now)));` – Mark Apr 13 '16 at 13:31
1

DbFunctions.TruncateTime(DateTime.Now) = DateTime.Today ? – Johann Blais Apr 14 '16 at 20:05
2

I would more appreciated this answer if it was contains an example of using those functions, Or at least make reference to some resource which contains some example – Hakan Fıstık Apr 25 '16 at 13:03
what about EF Core 1.0? – Afshar Mohebi Dec 11 '16 at 11:05
The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – SAR May 07 '17 at 10:14

Pranay Rana · Answer 2 · 2012-11-23T06:04:26.407

20

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.

edited Nov 23 '12 at 06:04

answered Nov 22 '12 at 11:35

Pranay Rana

164,177
33
228
256

It check for ==, not <=. Of course with some more conditions that can be achieved. – Tilak Nov 22 '12 at 11:43
come on, you never heard of DbFunctions.TruncateTime? – Toolkit Apr 22 '15 at 04:41
This becomes longwinded if you need to check a date using the comparator ''. – Force444 Jul 22 '15 at 12:09
1

Is there at TruncateOffset function to get the DateTime part of a DateTimeOffset? Seems impossible to refer to the DateTime part of a DateTimeOffset with entity framework, which makes it quite useless. – Triynko Dec 14 '16 at 05:05
hi, can you wrap this in an EF extension so that it can be reused all over? – aggie Dec 17 '17 at 06:25
This gets messy really fast. – Noldy Feb 20 '18 at 13:18

score 4 · Answer 3 · edited Sep 28 '13 at 22:07

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";

Sorry to tell you that Exception return `The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.` — Frank Myat Thu, Nov 22 '12 at 11:11
@Frank - thanks for the reply ..just forget to include ToList() function ...so code is updated in answer it like this after adding list function var _My_ResetSet_Array = _DB .tbl_MyTable .Where(x => x.Active == true) .Select(x => x).ToList() — Pranay Rana, Nov 22 '12 at 11:28
@Frank- i also provided one work around try out that this will might work for you... — Pranay Rana, Nov 22 '12 at 11:33
-1 for "First fetch the data and then compare", assuming a 1TB database with active records, you will download all just to remove 700GB (in the time range). — Rafael Herscovici, Jul 20 '15 at 11:48
The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. !!!!!! — DmitryBoyko, Apr 10 '18 at 08:23

score 2 · Answer 4 · edited Jan 09 '15 at 07:19

2

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

edited Jan 09 '15 at 07:19

Danubian Sailor

21,505
37
137
211

answered Jan 09 '15 at 06:15

Jatin

29
1

score 1 · Answer 5 · answered Oct 08 '13 at 19:10

1

&& x.DateTimeValueColumn <= DateTime.Now

This is supported so long as your schema is correct

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

answered Oct 08 '13 at 19:10

Joe Stellato

518
7
28

Except it's not. He's using Linq to Entities, not normal linq. – Slight Jan 27 '16 at 16:14

score 1 · Answer 6 · answered Oct 09 '13 at 11:32

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);

score 0 · Answer 7 · edited Sep 20 '18 at 14:40

0

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))
     }

edited Sep 20 '18 at 14:40

Karthic G

858
1
11
23

answered May 21 '18 at 19:07

Soharab Shaikh

3
3

1

Please wrap your code sample in code block formatting for easy reading. – Collin M. Barrett May 21 '18 at 19:19
I am considering the query performance since you are using string value to compare. – Frank Myat Thu May 22 '18 at 04:16
Thank u man... This code can combine the date to a string and help to search the date from the database to a list... – Soharab Shaikh May 22 '18 at 11:10
What type of error ? can you explain please... @habib – Soharab Shaikh Jun 03 '18 at 18:23
@Soharab you query will work definitely with lEnumberable but not with IQueryable because entity framework will say oh I don't know what is meant by.Month or Value.Day because entity framework first converts this into the query and then run. For further please try this you will be know what error maybe. – habib Jun 04 '18 at 15:11

score 0 · Answer 8 · answered Sep 21 '18 at 07:13

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");

score 0 · Answer 9 · answered Feb 06 '20 at 15:43

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();

score -4 · Answer 10 · answered Nov 22 '12 at 10:42

-4

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

answered Nov 22 '12 at 10:42

igrimpe

1,755
11
12

3

see the solution in my answer ...Date property will not work in linq to entity – Pranay Rana Nov 22 '12 at 11:37

Linq where clause compare only date value without time value

10 Answers10

Linked

Related