How to extract 30 days back data from datetime parameter

Question

I want to use the date parameter in the data table of my dotnet core's rdlc report. One of the two values ​​of the SQL parameter is the parameter value, the other is a date 30 days back from the date of the parameter value. But the second parameter is not working.

   public DataTable Selllrecord(DateTime datevale,int customerid)
   {
       var dt = new DataTable();
       using (SqlConnection conn = new SqlConnection(constar))
       {
           SqlCommand cmd = new SqlCommand("Select date, credited,debited from tbl_Customerladgers where CustomerID=@d1 and Date between @d2 and  @d3 order by date Desc", conn);
       
           cmd.Parameters.Add("@d1", SqlDbType.Int).Value = customerid;
           cmd.Parameters.Add("@d2", SqlDbType.Date).Value = datevale;

           //here problem
           cmd.Parameters.Add("@d3", SqlDbType.Date).Value = datevale.AddDays(-30).Date;
       
         
         conn.Open();
           SqlDataAdapter da = new SqlDataAdapter(cmd);
           da.Fill(dt);
           conn.Close();
       }
       return dt;
   }

Answer 1

Hi @jewel,

The syntax for BETWEEN usually requires the range to be from a smaller value to a larger value, so make sure the range is correct when you provide the arguments, i.e. @d2 should be the smaller date and @d3 the larger date.

So please try below code and tell me if it works for you.

public DataTable Selllrecord(DateTime datevale, int customerid)
{
    var dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(constar))
    {
        SqlCommand cmd = new SqlCommand("Select date, credited, debited from tbl_Customerladgers where CustomerID=@d1 and Date between @d2 and @d3 order by date Desc", conn);
        cmd.Parameters.Add("@d1", SqlDbType.Int).Value = customerid;
        cmd.Parameters.Add("@d2", SqlDbType.Date).Value = datevale.AddDays(-30).Date;
        cmd.Parameters.Add("@d3", SqlDbType.Date).Value = datevale.Date;
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        conn.Close();
    }
    return dt;
}

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Jason