Querying between two date values in asp.net mvc5

Question

I have a treatmentDate column in the database where dates to treated patients are submitted. If I need to generate report of Total Sum say between January 01 2015 and Jan 30 2015 for a particular company, how do I construct my query.

Below is what I have done and I'm having errors

            var treatmentSum = (from s in db.Treatments
                                where (s.CompanyId == CompanyID)
                                s.TreatmentDate >= fromDate && s.TreatmentDate <= toDate
                                select s.Amount).Sum();                
            ViewBag.TreatmentSum = treatmentSum;

Here's the treatment date column I want to search through. enter image description here

Your guidance will be highly appreciated.

shouldn't there be an '&&' between (s.CompanyId == CompanyID) AND s.TreatmentDate — Dawood Awan
– Dawood Awan, Commented May 15, 2015 at 13:28
Any luck with the answers below: stackoverflow.com/a/30263549/1837329 — IndieTech Solutions
– IndieTech Solutions, Commented May 16, 2015 at 18:57

IndieTech Solutions · Accepted Answer · 2015-05-16 18:57:03Z

2

You didn't post the error message. You are trying to compare strings using the <= and >= , instead try to handle all of your Date and Time variable as DateTime properties. Here's a piece of code for the LINQ expression:

DateTime FromDate= Convert.ToDateTime("04/20/2015");
DateTime ToDate= Convert.ToDateTime("04/28/2015");
ViewBag.TreatmentSum = GetAllTreatments.
Where(c=>Convert.ToDateTime(c.TreatmentDate)>=FromDate &&
Convert.ToDateTime(c.TreatmentDate)<=ToDate).
Sum(c=>c.Amount);

Also Here's a link to a demo for your specific problem : https://dotnetfiddle.net/1a50HI

edited May 16, 2015 at 18:57

answered May 15, 2015 at 15:40

IndieTech Solutions

2,5391 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guzzyman Over a year ago

It worked like a charm. Thanks a bunch for your help. The resukts was generated and sent to a PDF view. So I can see the results directly in PDF now. Any ideas on how to take the entire PDF view and save in into the database? I used the rotavita PDF generator.

IndieTech Solutions Over a year ago

Post the code on a separate question for that and me or someone else will definitely help.

Guzzyman Over a year ago

Hi thank once again. I have posted the question as suggested. You can find it here. link

Community · Accepted Answer · 2017-05-23 12:02:29Z

1

You've got some formatting issues:

  var treatmentSum = (from s in db.Treatments
                            where s.CompanyId == CompanyID &&
                            s.TreatmentDate >= fromDate && s.TreatmentDate <= toDate
                            select s.Amount).Sum();                
        ViewBag.TreatmentSum = treatmentSum;

You had a where in the middle of the statement:

where (s.CompanyId == CompanyID) and you weren't separating it with &&

Edit

As stated the dates are in string, you could convert them inline, although be aware you may need to provide some CultureInfo so the formatter knows how to convert the date:

  var treatmentSum = (from s in db.Treatments
                                where s.CompanyId == CompanyID &&
                                Convert.ToDateTime(s.TreatmentDate) >= fromDate && Convert.ToDateTime(s.TreatmentDate) <= toDate
                                select s.Amount).Sum();

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered May 15, 2015 at 13:29

nik0lai

2,66524 silver badges37 bronze badges

1 Comment

Guzzyman Over a year ago

I added the && to the query, I ran into another red squiggly line saying Operator '>=' cannot be appied to operands of type 'string and 'string' I actually have my date column in the database as a string datatype then used the datetime data annotation In my BOL.

Collectives™ on Stack Overflow

Querying between two date values in asp.net mvc5

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related