Issue with GridView conditional formatting on DatTime2

Question

Hi, I am wanting to do a conditional format based on a time difference but I am so far not being successful with it.

 

The database holding the value is 'SQL Server 2016'

 

The database is called 'DataLive'

 

The tables are called 'CurrentJobs' and 'CodeScans' which are joined together via a unique value which works perfect.

 

DataLive.CurrentJobs.JobDue is of type DateTime and this cannot be altered.

 

DataLive.CodeScans.ScanDate is of type DateTime2(7), Any date that goes into this column shows as '2019-08-31 13:15:41.0000000' when I perform a SELECT from the table.

 

A view 'JobScansView' has been set up for the combined tables, which is accessed via a GridView1 on index.aspx.

 

'ScanDate' column shown on the webpage shows the data as '31-08-2019 13:15:41', so there appears to be some kind of translation on the date there.

 

Now, I have so far been able to conditionally format the rows as needed but this last one has me pickled.

 

I want to flag the row up in red, if the difference in time between ScanDate and DateTime.Now is greater than 4 hours.

 

The second thought on this is to show some kind of image of a Tick or X in a new cell in this GridView1 depending on the difference.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
string JobPriority = Convert.ToString(GridView1.Rows[i].Cells[0].Text);
DateTime JobDue = Convert.ToDateTime(GridView1.Rows[i].Cells[1].Text); // <---- This bit shows error //
DateTime ScannedTime = Convert.ToDateTime(GridView1.Rows[i].Cells[6].Text);
if (JobPriority == "B")
{
GridView1.Rows[i].BackColor = Color.Yellow;
}
else if (JobPriority == "A")
{
GridView1.Rows[i].BackColor = Color.LightGreen;
}
else if (JobDue < DateTime.Now)
{
GridView1.Rows[i].BackColor = Color.LightBlue;
}
else if (JobDue == DateTime.Now && JobDue <= DateTime.Today.AddDays(1) )
{
GridView1.Rows[i].BackColor = Color.Orange; } // <---- This bit below doens't work ----> //
else if (ScannedTime < DateTime.Now.AddHours(-4)) // //
GridView1.Rows[i].BackColor = Color.Red; //
GridView1.Rows[i].ForeColor = Color.Black; //
} // <---- end of bit that doesn't work ---->
}
}

When I run the page with the commented out code it works fine, however if I uncomment, I get the following error:

 

String was not recognized as a valid DateTime but only on the ScanDate column that is DateTime2(7).

 

Any ideas where I can look for an answer?

 

Note, on reading the post, I can see I need to do a little work on the condition on the between DateTime.Now and DateTime.Today.AddDays(1) because it's not right.

 

Any help here would also be appreciated.