Search code examples
t-sqlsql-server-2008-r2iif

SQL Server 2008 r2: TSQL IIF Query not recognized

I'm trying to use IIF for the first time instead of a CASE and SQL Server is throwing this error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '>'.

My code:

DECLARE @lDate date = CONVERT(date,GETDATE());
DECLARE @lMonth int = MONTH(@lDate);
DECLARE @lDay int = DAY(@lDate);

DECLARE @lPeriodStart date, @lPeriodEnd date, @lPayPeriod int, @lCutOffDay int = 14;

--there are 24 pay periods find which one
SET @lPayPeriod = @lMonth * 2 - IIF(@lDay > @lCutOffDay, 0, 1);

SELECT @lPayPeriod

I don't understand how this is different from the guidelines from MSDN.

Solution

  • IIF was introduced in SQL Server 2012. As you've tagged your question with SQL Server 2008 I'd say that is the source of the problem.

    Your syntax is valid and the code would work in a newer version.