Datetime comparison before and after

Question

I'm using dynamic linq to build a where clause for a moderately complex search, and I've run into a problem with dates.

I want the user to be able to filter on the "date created" searching for anything either before that date, on that date, or after that date.

I found this answer which I used to construct a dynamic query looking like this: .Year == @0.Year && created.Month == @0.Month && created.Day == @0.Day)". This works fine for searching for dates which are exactly equal, but doesn't hold up when trying to search for something before or after. The furthest I've gotten is doing "(created.Year >= @0.Year && created.Month >= @0.Month && created.Day > @0.Day) which doesn't work for anything outside the current month (If I searched for (after today), it would only show me results with day > 27 even if they're in a month after the date I searched for.)

I tried using DateTime.Compare as well but that didn't work, nor did trying to compare DateTime.Date`.

Is there any way I can accomplish this short of using T-SQL directly?

EDIT: with "(created.Date == @0.Date)"

enter image description here

Why not simply `created.Date > @yourDate.Date` or `created.Date < @yourDate.Date`? (Assuming `created` and `@yourDate` are of type `DateTime`) — Corak, May 27 '13 at 14:16
I would be *very* surprised if it supported `.Day`, `.Month` and `.Year`, but not `.Date`. — Corak, May 27 '13 at 14:26
Okay, you got me surprised. Well, then have `@yourDate = whatEverYouGotFromUser.Date` and then `created < @yourDate` or `created > @yourDate`. — Corak, May 27 '13 at 15:09
@Corak problem is then that breaks equals (date) so I was hoping for a better way. However, if nothing else comes up I'll use that - thanks! — Mansfield, May 27 '13 at 15:26
for equals, you might be able to do something like `created > @yourDate && created < @yourDate.AddDays(1)` ... but now I'm not sure anymore. ^^; you might need to make `@yourDate.AddDays(1)` another variable. — Corak, May 27 '13 at 15:58

score 0 · Accepted Answer · answered May 27 '13 at 17:04

I ended up using my existing code for "On" comparisons, and just doing a strict > or < for before and after as Corak suggested.

if (Comparison == ComparisonType.Equals) {
    return string.Format("({0}.Year {2} @{1}.Year && {0}.Month {2} @{1}.Month && {0}.Day {2} @{1}.Day)", FieldName, index, ComparisonType.Equals.Evaluate()); //workaround for "exactly equals date"
} else if (Comparison == ComparisonType.Less_Than) {
       Value = ((DateTime)Value).Minimize(); //set the time values of the date to the minimums (00:00:00 000)
} else if (Comparison == ComparisonType.Greater_Than) {
       Value = ((DateTime)Value).Maximize(); //set the time values of the date to the maximums (23:59:59 999)
}

return string.Format("({0} {1} @{2})", FieldName, Comparison.Evaluate(), index);

Datetime comparison before and after

1 Answers1