sqldatediffsql-viewintersystems-cache

SQL Query returns a negative number but does not interpret it as a negative number but a positive number

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"
Solution

  • 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.

    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.