DateTime.Compare in Linq

Question

I am trying to use DateTime.Compare in Linq :

from ---  
where DateTime.Compare(Convert.ToDateTime(ktab.KTABTOM), DateTime.Now) < 1
select new 
{
-------
}

But this gives me an error :

LINQ to Entities does not recognize the method 'System.DateTime ConvertTimeFromUtc(System.DateTime, System.TimeZoneInfo)' method, and this method cannot be translated into a store expression

This link suggests that we should use EntityFunctions to fix dateTime manipulation in Linq. But here I need to compare the complete date. Even this won't help me.

The date is in format yyyy-MM-dd.

Why are you storing date information in your database as a string? The reason you don't do that is to avoid problems like this. — Scott Chamberlain, Nov 20 '13 at 07:12
@ScottChamberlain : KTABTOM is nullable DateTime. Plus I don't have control on the database. I will suggest this to concerned people. But can you provide a solution for this as of now? — Nitish, Nov 20 '13 at 07:18
if `ktab.KTABTOM` is a nullable DateTime why do you call `Convert.ToDateTime` and not compare it directly? also do you want NULL's to be included in the results? — Scott Chamberlain, Nov 20 '13 at 07:20
Because if you are comparing nullable and non nullable values, you need to cast. — Nitish, Nov 20 '13 at 07:27
@Nitish it is `LINQ to entities` so your query will be translated into sql so there is no need to cast — Guru Stron, Nov 20 '13 at 07:29
you don't need `DateTime.Compare` just write `ktab.KTABTOM < DateTime.Now` — Guru Stron, Nov 20 '13 at 07:31
@Nitish and `Compare` has next returns: `Less than zero - t1 is earlier than t2.`, `Zero - t1 is the same as t2.`, `Greater than zero - t1 is later than t2.` What are you trying to achieve by comparing result with `1`? — Guru Stron, Nov 20 '13 at 07:34
@Guru he is trying to get dates less than or equal to "now". — Eren Ersönmez, Nov 20 '13 at 07:36
@ErenErsönmez wouldn't it be `<= 0` or i need a lot of more coffee right now? — Guru Stron, Nov 20 '13 at 07:38
@ErenErsönmez yeah) forgot that `Compare` returns `int`, thanks) — Guru Stron, Nov 20 '13 at 07:42
@ErenErsönmez in first comment OP has said that KTABTOM is nullable `DateTime` — Guru Stron, Nov 20 '13 at 07:47

Guru Stron · Accepted Answer · 2013-11-20T08:03:24.523

You don't need DateTime.Compare just write ktab.KTABTOM <= DateTime.Now

Examples with nullable DateTime:

doesn't compile

from p in Projects
where DateTime.Compare(DateTime.Now, p.EndDate) <= 0
select p.EndDate

and

from p in Projects
where DateTime.Now <= p.EndDate
select p.EndDate

translates into

SELECT 
[Extent1].[EndDate] AS [EndDate]
FROM [dbo].[Project] AS [Extent1]
WHERE  CAST( SysDateTime() AS datetime2) <= [Extent1].[EndDate]

Examples without nullable DateTime:

from p in Projects
where DateTime.Compare(DateTime.Now, p.StartDate) <= 0
select p.StartDate

and

from p in Projects
where DateTime.Now <= p.StartDate
select p.StartDate

both translates into

SELECT 
[Extent1].[StartDate] AS [StartDate]
FROM [dbo].[Project] AS [Extent1]
WHERE (SysDateTime()) <= [Extent1].[StartDate]

Bear in mind that in .NET, comparing DateTime directly will ignore time zones, and count only ticks, so that if the DateTimes are not both in the same time zone, the comparison will be wrong. — Falcon Momot, May 08 '16 at 09:51

DateTime.Compare in Linq

1 Answers1