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, May 15 '15 at 13:28
Any luck with the answers below: http://stackoverflow.com/a/30263549/1837329 — IndieTech Solutions, May 16 '15 at 18:57

IndieTech Solutions · Accepted Answer · 2015-05-16T18:57:03.083

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 '15 at 18:57

answered May 15 '15 at 15:40

IndieTech Solutions

2,527
1
21
36

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. – Guzzyman May 17 '15 at 20:28
Post the code on a separate question for that and me or someone else will definitely help. – IndieTech Solutions May 17 '15 at 21:09
Hi thank once again. I have posted the question as suggested. You can find it here. [link](http://stackoverflow.com/questions/30298009/) – Guzzyman May 18 '15 at 08:09

score 1 · Answer 2 · edited May 23 '17 at 12:02

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

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. — Guzzyman, May 15 '15 at 13:39

Querying between two date values in asp.net mvc5

2 Answers2