subtraction of dates

ta mu

i want to make a connection with database which will work on windows form load event
and populate records in datagrid view which are of last five days mean select all records by subtracting 5 days from present date.
plz help me i have no idea.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
Here it is

DateTime date = dateTimePicker1.Value.AddDays(-5); // will substract 5 days from selected date
DataTable dt = new DataTable(); //initializing the datatable
using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUserId;Password=MyPassword")) //creating a new sql connection by passing the connection string
{
if (conn.State == ConnectionState.Closed) //checking the connection state
conn.Open(); //opening the connection
string sql= "select *from YourTable where DateColumn>=@Field"; //sql query to select data where DateColumn value is greater than date variable
SqlCommand cmd = new SqlCommand(sql, conn); //creating the sql command
cmd.Parameters.AddWithValue("@Field", date); //adding the parameters to the sql query
SqlDataAdapter dap = new SqlDataAdapter(cmd); //creating data adapter
dap.Fill(dt); //filling the DataTable
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
dataGridView1.DataSource = dt; //binding the datatable to gridview
}

Regards,
Iftikar
0
Iftikar Hussain13y ago
try like this

DateTime date = dateTimePicker1.Value.AddDays(-5);
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUserId;Password=MyPassword"))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
string sql= "select *from YourTable where DateColumn>=@Field";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Field", date);
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(dt);
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
dataGridView1.DataSource = dt;
}

Regards,
Iftikar
0
ta mu13y ago
this code is selecting records before last five days. how i can select records of last five day what thing i need to change because i cant under stand this code. plz explain with comments
if you can
thank you
0
Iftikar Hussain13y ago
try like this

DateTime date = dateTimePicker1.Value.AddDays(-5);
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUserId;Password=MyPassword"))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
string sql= "select *from YourTable where DateColumn<=@Field";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Field", date);
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(dt);
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
dataGridView1.DataSource = dt;
}

Regards,
Iftikar
0
Vulpes13y ago
Try:

string command = "SELECT * FROM Table WHERE DateColumn = '" + dateTimePicker1.Value.AddDays(-5).ToString("dd/MM/yyyy") + "'";
0
ta mu13y ago
these are the sql qurries i need c# code
1. like the crrunt time will be "dateTimePicker1.Value" how to minus 5 day from it and select
2. records of that days
0
Iftikar Hussain13y ago
Hi,
Try like this

select *FROM YourTable DATEDIFF(dd,GETDATE(),YourDateColumn)<=5

Regards,
Iftikar
0
Riyaz Akhtar13y ago
use your SQL query like below ,

SELECT * FROM TABLENAME WHERE dateColumnname >= convert(varchar(10), DATEADD(DAY, -5, GETDATE()), 120) AND dateColumnname<=GETDATE()

This query will fetch last 5 days records
0
ta mu13y ago
i want to select records from database table and populate them in datagrid view in window form
0
Riyaz Akhtar13y ago
are you storing date in that table ?
0