Hi, I have a gridview which refers to a database table, with two datetime columns. I have the following code running to conditionally format the backcolour if the difference between those two dates is more than 4 hours, but I would like to be able to create
an extra column where the value will be '0' if the difference is less than 4 hours, or '1' if the difference is more than 4 hours, and '2' one of the dates is blank. is there a way to do this?
How are you binding your GridView? You could add another column in your database Select that is generated from that calculation.
An example:
SELECT [scantime],[otherfields],
(CASE
WHEN scantime IS NULL THEN 2
WHEN DATEDIFF(hour, scantime, GETDATE()) < 4 THEN 0
WHEN DATEDIFF(hour, scantime, GETDATE()) >= 4 THEN 1
ELSE 2 END)
AS diffhours FROM [jobs]
Then add a new readonly boundfield to your GridView for the new column "diffhours".
Oddly enough, after I posted, I realised from previously working with Microsoft Access, that you can calculate a separate column and display that, so wondered if SQL would do the same.
Currently the Gridview pulls data from a View but i'm positive I can duplicate it to test.
that you can calculate a separate column and display that, so wondered if SQL would do the same.
i tested in SQL, it works fine.
The result:
Best regards,
Sam
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
I did note during the course of last night that the rule seems to fall over a little when we approach the 4 hour timer, it seems to cause the output to change if the hour difference is right but doesn't seem to take the actual time into account.
i.e. time of 16.42, plus 4 hours should be 20.42 and then trip over the 4 hour marker, however..
16.42, and checking again at 20.01, the hours difference is correct but obviously we're still 41 minutes before 4 hours has actually passed.
Member
3 Points
8 Posts
GridView with unbound columns
Sep 03, 2019 10:57 AM|techmandem|LINK
Hi, I have a gridview which refers to a database table, with two datetime columns. I have the following code running to conditionally format the backcolour if the difference between those two dates is more than 4 hours, but I would like to be able to create an extra column where the value will be '0' if the difference is less than 4 hours, or '1' if the difference is more than 4 hours, and '2' one of the dates is blank. is there a way to do this?
Contributor
5961 Points
2466 Posts
Re: GridView with unbound columns
Sep 03, 2019 12:59 PM|KathyW|LINK
How are you binding your GridView? You could add another column in your database Select that is generated from that calculation.
An example:
Then add a new readonly boundfield to your GridView for the new column "diffhours".
Member
3 Points
8 Posts
Re: GridView with unbound columns
Sep 03, 2019 01:38 PM|techmandem|LINK
Oddly enough, after I posted, I realised from previously working with Microsoft Access, that you can calculate a separate column and display that, so wondered if SQL would do the same.
Currently the Gridview pulls data from a View but i'm positive I can duplicate it to test.
Thanks again Kathy, much appreciated!
Contributor
3370 Points
1409 Posts
Re: GridView with unbound columns
Sep 04, 2019 09:38 AM|samwu|LINK
Hi techmandem,
i tested in SQL, it works fine.
The result:
Best regards,
Sam
Member
3 Points
8 Posts
Re: GridView with unbound columns
Sep 04, 2019 10:22 AM|techmandem|LINK
Hi Sam / Kathy, thank you again for the assist.
I did note during the course of last night that the rule seems to fall over a little when we approach the 4 hour timer, it seems to cause the output to change if the hour difference is right but doesn't seem to take the actual time into account.
i.e. time of 16.42, plus 4 hours should be 20.42 and then trip over the 4 hour marker, however..
16.42, and checking again at 20.01, the hours difference is correct but obviously we're still 41 minutes before 4 hours has actually passed.
Is there a minutes version of the DIFF?
Contributor
5961 Points
2466 Posts
Re: GridView with unbound columns
Sep 04, 2019 03:02 PM|KathyW|LINK
If you want to compare to the minute, change to DATEDIFF(minute, ...), and change 4 to 240 (4 hours is 240 minutes).