CODEWITHSUNDEEP
c#linqdatetime
Chase
Chase

Reputation: 564

Compare past dates in LINQ

I have to write a LINQ statement that finds the number of times an item appears in a database over the past 90 days. That's easy but they are wanting to see the data in terms of 1-30 days, 31-60 days, and 61-90 days.

What I'm currently doing is creating a model that has the four sets of data, getting a list of all the unique SKU's, and then finding how many times they appear in the data base.

My problem is the date ranges. I'm create new DateTime objects and adding NEGATIVE days to them and I'm trying to use that to compare ranges. I can't figure out how to say something like this:

Edited date is between (-30 days) and (-60 days).

I can't use a SP for this. There has to be an easier way. Maybe using a time span and seeing if it falls in that span? I'm not sure. I rarely work with dates.

Upvotes: 2

Views: 6160

Answers (5)

ozwizard
ozwizard

Reputation: 1

this seemed to work for me VB.NET 3.5 returns a list of items in the past 10 minutes ... I'm sure it can be adjusted to meet your needs

    ' if online time is greater than 10 minutes ago pickup user                                check onLineTime.Date = now.AddMinutes(-10).Date              check onLineTime.TimeOfDay = now.AddMinutes(-10).TimeOfDay
     Dim rsl = From s In db.tblSessions Where (s.SessionID <> context.Session.SessionID AndAlso s.onLineTime.Date = DateTime.Now.AddMinutes(-10).Date AndAlso s.onLineTime.TimeOfDay > DateTime.Now.AddMinutes(-10).TimeOfDay)

where s.onLineTime is a SQL DATETIME field ie. 2015-02-11 04:49:26.283

Upvotes: 0

Manual5355
Manual5355

Reputation: 982

Why don't you do something like

        TimeSpan beginning = TimeSpan.FromDays(30);
        TimeSpan end = TimeSpan.FromDays(60);

        var query = from item in database
                    let difference = DateTime.Now - item.TimeStamp
                    where difference > beginning &&
                          difference < end
                    select item;

Upvotes: -1

James Michael Hare
James Michael Hare

Reputation: 38397

Hard to write a LINQ query when unsure of your structure. But to get that date range you can do something like this in code:

var difference = laterDate - earlierDate;

if (difference <= TimeSpan.FromDays(60) && difference >= TimeSpan.FromDays(30)) ...

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499760

Something like:

DateTime today = DateTime.Today;
DateTime minusThirty = today.AddDays(-30);
DateTime minusSixty = today.AddDays(-60);

// Normally I wouldn't use a query expression for just a single where clause,
// but I assume you want more...
var query = from item in db.Table
            where item.Date >= minusSixty && item.Date < minusThirty
            select item;

You may want <= and > instead - you should work that out for yourself. However, you almost certainly want one bound to be inclusive and one bound to be exclusive, and use those consistently - that way no item will end up in more than one bucket.

Upvotes: 6

msarchet
msarchet

Reputation: 15232

DateTime date1 = DateTime.Now().AddDays(-30);
DateTime date2 = DateTime.Now().AddDays(-60);

if (yourDate.Date >= date2.Date && yourDate.Date <= date1.Date)
{
   //DoSomething
}

Upvotes: 1

Related Questions