Little SQL Server Tricks: The IIF Function

I had a query where I had to find the larger of two values. The MAX() function may sound like the thing you could use, but that function only works with aggregates and GROUP BY. I had to search for an alternative and found the IIF function.

IIF( boolean_expression, true_value, false_value )

The IIF function is a shorthand for writing a CASE expression with its WHEN ... THEN ... ELSE keywords.

Given we have the two columns dateOne and dateTwo. To get back the bigger value of those two columns, we can write this statement:

IIF(dateOne > dateTwo, dateOne, dateTwo)

For my use case that was enough and much shorter to type than a CASE WHEN THEN ELSE expression I used in the past.