When I run the query it is doing a DATEDIFF and getting a negative number as that is correct but its not interpreting the integer as a negative but as a positive number causing to use the wrong color to print as it should be red. What am I missing to make this work on negative numbers that should print red when the return datediff is <0.

Select
STRING(
case 
when datediff('hh',getdate(), ( CONVERT (CHAR(10),Table.end_date,121) ||' '|| Table.end_time ) ) > 7 
and "Table"."column" = '12' or "Table"."column" = 'JE'
then '</font><font color = green ><b>'
when datediff('hh',getdate(), ( CONVERT (CHAR(10),Table.end_date,121) ||' '|| Table.end_time ) ) > 2
and
datediff('hh',getdate(), ( CONVERT (CHAR(10),Table.end_date,121) ||' '|| Table.end_time ) ) <= 7
and "Table"."column" = '12' or "Table"."column" = 'JE'
then '</font><font color = yellow><b>'
when datediff('hh',getdate(), ( CONVERT (CHAR(10),Table.end_date,121) ||' '|| Table.end_time ) ) < 2
and "Table"."column" = '12' or "Table"."column" = 'JE'
then '</font><font color = red><b>'
else  '</font><font color = black >' end ,"Table"."column") AS "column"

CodePudding user response：

You likely have an operator precedence problem.

Your conditions are variations of diff and this or that. and has a higher precedence than or. one and two or three really means...

(diff and this) or that.

in other words, if that is true it will always be true. In call your cases if "Table"."column" = 'JE' is true the whole statement will be true. That means you're always going to get green or black.

You probably mean

diff and (this or that)

And

(diff and diff) and (this or that)

Other notes.

• Turn end_date and end_time into proper date and time columns.
• Add an end_at timestamp column which already concatenates them.

Both of those will make queries simpler and faster; you don't have to convert a string, and comparisons like the above will be able to use an index on end_at.

• Don't quote table and column names unless you have to, it makes them case-sensitive.