CODEWITHSUNDEEP
c#asp.netsql
user1455232
user1455232

Reputation: 43

How to query for data in a Gridview between a day or week or Month or year

I have tried to show my data from database. If I stored some data today, that alone should display in the GridView.

I'd like to be able to show by week, month, and year.

My code:

public void gettoday()
    {
        con.Open();
        {

            //string strview = "select MRNO,MaterialCode,Description,Specification,Category as Cat,Source as Sor,Population as Pop, StockInStores as Stock,MRRating as Rating,PreparedBy,PreparedDate,CheckedBy,CheckedDate,ApprovedBy,ApprovedDate  from tbl_KKSMaterialRaise where PreparedDate between DateAdd(day,-1,GetDate()) AND DateAdd(day,+1,GetDate())";
            string strview = "select MRNO,MaterialCode,Description,Specification,Category as Cat,Source as Sor,Population as Pop, StockInStores as Stock,MRRating as Rating,PreparedBy,PreparedDate,CheckedBy,CheckedDate,ApprovedBy,ApprovedDate  from tbl_KKSMaterialRaise where PreparedDate between (getdate()-1) and (getdate()+1) order by PreparedDate";
            SqlCommand cmd = new SqlCommand(strview, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                GridView1.DataSource = ds;
                GridView1.DataBind();
                //SqlDataReader reader = cd.ExecuteReader();
            }

            else
            {
                GridView1.DataSource = null;
                GridView1.DataBind();
            }

        }
        con.Close();


    }

Upvotes: 0

Views: 1985

Answers (1)

Josh Darnell
Josh Darnell

Reputation: 11433

You would just need to change the BETWEEN statement in your WHERE clause. Right now you have:

where PreparedDate between (getdate()-1) and (getdate()+1)

For a week, you'd need to use something like this:

WHERE PreparedDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, 1, GETDATE())

You could do similar things for month and year by changing DATEADD(DAY, -7, GETDATE()) to the appropriate date range.

For month

DATEADD(MONTH, -1, GETDATE())

And for year:

DATEADD(YEAR, -1, GETDATE())

Upvotes: 1

Related Questions