locked
How to use/pass date parameter in Linq query RRS feed

  • 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