CODEWITHSUNDEEP
c#sqlasp.net
Ibtisam Tanveer
Ibtisam Tanveer

Reputation: 117

How to calculate difference of date using sql

I am trying to extract the last 7 days total number of requests from my table named "Request". It has fields Date and Time and other records as well. Following is my piece of code:

 public void TotalRequest()
    {
        DateTime d1=DateTime.Now;
        DateTime d2=DateTime.Now.AddDays(-7);
        TimeSpan t=d1-d2;
        double days=t.TotalDays;
        SqlConnection MySQL = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        string total_req = "select count(*) from Request where Date>='" + days + "'";
        SqlCommand com=new SqlCommand(total_req,MySQL);
        MySQL.Open();
        int Total_Requests = Convert.ToInt32(com.ExecuteScalar().ToString());
        MySQL.Close();
        Response.Write(Total_Requests.ToString());
    }

The error is "Conversion failed when converting date and/or time from character string.". Please guide and thanks in advance for your valuable time.

Upvotes: 2

Views: 57

Answers (1)

btevfik
btevfik

Reputation: 3431

You need to provide a type of Date to your sql because it cannot convert 'days'. You already have d2 as 7 days earlier from now so do this.

string total_req = "select count(*) from Request where Date>='" + d2.ToString() + "'";

Upvotes: 2

Related Questions