Answered by:
How to use/pass date parameter in Linq query

Question
-
User-356787864 posted
How do I write the c# code for this line:
Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == "10/09/2013").Sum(r => (Double)r["CALLS"]);
Here I want to replace the "10/9/2013" with a variable value and "Calls" with a variable like this:
Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == myDate).Sum(r => (Double)r[sumBy]);
I tried this:
string ymd = "2013-10-09"; // this definitely will come from database column
System.DateTime myDate = Convert.ToDateTime(DateTime.Parse(ymd).ToString("M/dd/yyyy"));
var sum = dtCurrentDateRows.AsEnumerable()
.Where(r => r.Field<System.DateTime>("DATE") == myDate)
.Sum(r => r.Field<Double>("CALLS"));This gives error "Specified cast is not valid";
Friday, June 27, 2014 6:28 AM
Answers
-
User-760709272 posted
If DATE is a string you can't cast it to DateTime which is what you are doing in your code. You shouldn't store dates as strings, but that's a separate matter.
string ymd = "2013-10-09"; // this definitely will come from database column System.DateTime myDate; DateTime.TryParse(ymd, out myDate); var sum = dtCurrentDateRows.AsEnumerable() .Where(r => (string)r["DATE"] == myDate.ToString("M/dd/yyyy")) // use whatever format the date is stored in in your database
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, June 27, 2014 8:47 AM
All replies
-
User-760709272 posted
System.DateTime myDate = Convert.ToDateTime(DateTime.Parse(ymd).ToString("M/dd/yyyy")); // this line takes a string, converts it to a date, converts it to a string, converts it to a date // just use either ToDateTime or the Parse or DateTime.TryParse. Dates don't have internal formats, // you can't convert a date from one format to another
string ymd = "2013-10-09"; // this definitely will come from database column System.DateTime myDate; DateTime.TryParse(ymd, out myDate); var sum = dtCurrentDateRows.AsEnumerable() .Where(r => r.Field<System.DateTime>("DATE") == myDate) .Sum(r => r.Field<Double>("CALLS"));
You have to watch for the "time" element though as your .net date has a time of 00:00:00 and if your SQL "DATE" column has a time of anything other than 00:00:00 then the comparison won't be valid.
Friday, June 27, 2014 6:42 AM -
User-356787864 posted
Even these lines gives me error: Specified cast is not valid.
This is the code:
string ymd = "10/9/2013";
System.DateTime myDate;
DateTime.TryParse(ymd, out myDate);
var sum = dtCurrentDateRows.AsEnumerable()
.Where(r => r.Field<System.DateTime>("DATE") == myDate)
.Sum(r => r.Field<Double>("CALLS"));Friday, June 27, 2014 6:51 AM -
User-760709272 posted
What SQL type is "DATE" and what type is "CALLS"?
Friday, June 27, 2014 6:56 AM -
User-356787864 posted
Date is string type and values in calls are Double.
Also when I write this :
Double n = dtCurrentDateRows.AsEnumerable().Where(r => (String)r["DATE"] == "10/09/2013").Sum(r => (Double)r["CALLS"]);
It gives me correct values but replacing the hard code values gets me either error or 0.0
Friday, June 27, 2014 7:00 AM -
User-760709272 posted
If DATE is a string you can't cast it to DateTime which is what you are doing in your code. You shouldn't store dates as strings, but that's a separate matter.
string ymd = "2013-10-09"; // this definitely will come from database column System.DateTime myDate; DateTime.TryParse(ymd, out myDate); var sum = dtCurrentDateRows.AsEnumerable() .Where(r => (string)r["DATE"] == myDate.ToString("M/dd/yyyy")) // use whatever format the date is stored in in your database
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, June 27, 2014 8:47 AM