how to compare date in database column & date_now, and put result in a column?

Question

i have a table in SqlServer which contain's: (ID, item_name, date_time_added)

i want to create a C# code to first: view (ID,item_name, date_time_added) column in datagridview then calculate (date_time_NOW - date_time_added) and view the result in a new column(named: expire's in:) in same datagridview...

Note: result would count day's remaining before expiring

what i've tried so far:

        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Expire's in:", typeof(int)));

        int countrow = dataGridView1.RowCount;

            for (int i = 0; i < countrow; i++)
            {

                string dateAsString = dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[3].Value.ToString();
                DateTime.TryParseExact(dateAsString , "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out DateTime dateAsString);

                dateTimePicker3.Text = dateAsString;
                DateTime expire_date = dateTimePicker3.Value;

                TimeSpan span = expire_date - DateTime.Now;
                int days = span.Days;
                dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[4].Value = days;

            }

Note:Code Updated... Any help will be greatly appreciated..

Answer 1

I will assume the “ExpireDate” field returned from the sql query is a DateTime object. If this is the case then it would appear that converting the “date” to a string is unnecessary. Example, given a “future” date, then the difference between todays date and the “future” date can be accomplished as…

TimeSpan dif = futureDate.Subtract(DateTime.Now);

Using a DataTable proffers the ability to use an Expression column, however, I do not think this will work with dates and times. Fortunately, this should not be difficult to implement if the grids DataSource is a DataTable. Using a “Class” would be another option. This example uses a DataTable as a DataSource to the grid.

Given this, to make things simple it would appear that a method that takes a DataRow from the data table and adds this TimeSpan difference may come in handy. It may look something like below…

private void SetDifCol(DataRow row) {
  TimeSpan dif = ((DateTime)row["ExpireDate"]).Subtract(DateTime.Now);
  row["TimeToExpire"] = dif.Days + " days " + dif.Hours + " hours " + dif.Minutes + " minutes";
}

Given that the DataTable has already been filled with the data… the code is going to have to “ADD” this difference column, then loop through each row and calculate the difference between the dates. Therefore, a small method that simply adds this column may look something like below…

private void AddDifferenceColumn(DataTable dt) {
  dt.Columns.Add("TimeToExpire", typeof(string));
}

Next is the loop through all the rows in the DataTable and simply call the SetDifCol method on each row.

private void CalculateDateDif(DataTable dt) {
  foreach (DataRow row in dt.Rows) {
    SetDifCol(row);
  }
}

This will work as expected when the data is loaded, however, what if the user “changes” one of the “ExpireDate” values in the grid? In this case, we would need to wire up one of the grids cell change events. Specifically the grids CellValueChanged event. This event will call the SetDifCol method if the “ExpireDate” value changes in that row…

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e) {
  if (dataGridView1.Columns[e.ColumnIndex].Name == "ExpireDate") {
    if (e.RowIndex >= 0 && dataGridView1.Rows[e.RowIndex].Cells["ExpireDate"].Value != null) {
      DataRowView row = (DataRowView)dataGridView1.Rows[e.RowIndex].DataBoundItem;
      SetDifCol(row.Row);
    }
  }
} 

Putting this all together may look something like below…

DataTable GridTable;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  GridTable = GetTable();
  FillTable(GridTable);
  AddDifferenceColumn(GridTable);
  CalculateDateDif(GridTable);
  dataGridView1.DataSource = GridTable;
  dataGridView1.Columns[3].Width = 180;
}


private DataTable GetTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("ID", typeof(string));
  dt.Columns.Add("Name", typeof(string));
  dt.Columns.Add("ExpireDate", typeof(DateTime));
  return dt;
}
private void AddDifferenceColumn(DataTable dt) {
  dt.Columns.Add("TimeToExpire", typeof(string));
}

private void FillTable(DataTable dt) {
  dt.Rows.Add("ID1", "Name1", new DateTime(2019, 12, 31));
  dt.Rows.Add("ID2", "Name2", new DateTime(2019, 8, 31));
  dt.Rows.Add("ID3", "Name3", new DateTime(2019, 4, 30));
  dt.Rows.Add("ID4", "Name4", new DateTime(2019, 1, 31));
  dt.Rows.Add("ID5", "Name5", new DateTime(2019, 4, 12, 21, 38, 00));
}

private void CalculateDateDif(DataTable dt) {
  foreach (DataRow row in dt.Rows) {
    SetDifCol(row);
  }
}

private void SetDifCol(DataRow row) {
  TimeSpan dif = ((DateTime)row["ExpireDate"]).Subtract(DateTime.Now);
  row["TimeToExpire"] = dif.Days + " days " + dif.Hours + " hours " + dif.Minutes + " minutes";
}

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e) {
  if (dataGridView1.Columns[e.ColumnIndex].Name == "ExpireDate") {
    if (e.RowIndex >= 0 && dataGridView1.Rows[e.RowIndex].Cells["ExpireDate"].Value != null) {
      DataRowView row = (DataRowView)dataGridView1.Rows[e.RowIndex].DataBoundItem;
      SetDifCol(row.Row);
    }
  }
}

I hope this helps.

EDIT: to change column type from string to int to sort numerically.

In reference to the extra question you posted, you commented that ”i want to calculate according to what is inside my db Table” … There is no code in this question or the other question that shows a data base. How are you getting the data to begin with?

It appears in this question that there IS a NEW DataTable dt and a column is added to it, however, it is NEVER used. The loop in the code simply adds the difference column to the “GRID” NOT the DataTable. My answer “adds” the diffence column to the DataTable (which you should do). I recommend you show how you are getting the data from the data base.

In reference to sorting the column, you have already noticed that strings that are numbers will not sort properly numerically. This is because they are string… solution… make them ints. Using my answer, two changes are need for this. First the creation of the column needs to be an int type…

private void AddDifferenceColumn(DataTable dt) {
  dt.Columns.Add("TimeToExpire", typeof(int));
}

Second a change is needed in the SetDifCol method. Since you only want the days difference and any values less than zero should show as zero (0), then the following changes should accommodate this requirement.

private void SetDifCol(DataRow row) {
  TimeSpan dif = ((DateTime)row["ExpireDate"]).Subtract(DateTime.Now);
  if (dif.Days >= 0) {
    row["TimeToExpire"] = dif.Days;
  }
  else {
    row["TimeToExpire"] = 0;
  }
}

These two changes should sort the column numerically as expected.

Lastly, it should be clear, that IF you want this “difference” column to be reflected in the database… then YOU will have to add the difference column to the database table, THEN, you will need to issue an update command to the database table.

Answer 2

From what I see you try to put a string into the DateTime value here:

DateTime str;
        str=dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[3].Value.ToString();

If you want to parse string to DateTime the code should look like this:

string dateAsString = dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[3].Value.ToString();
                DateTime.TryParseExact(dateAsString, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out DateTime dateAsDateTime);

Then you can substract that date from DateTime.Now:

TimeSpan span =  dateAsDateTime - DateTime.Now;

And finally extract the days from the span:

int days = span.Days;

OR just do it all in one line:

int days = (DateTime.Now - dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[3].Value).Days;