Search code examples
sqlsql-server-2014iif

IIF 'Incorrect syntax near' error at SQL Server 2014

I am trying date control by month with this script

DECLARE @Date DATETIME = '2015-07-31';
DECLARE @MonthCount INT = 3;
DECLARE @controlDate DATETIME = '2015-04-28';

SELECT
    MONTH(@controlDate),
    MONTH(DATEADD(MONTH, -@MonthCount, @Date)),
    IIF(MONTH(@controlDate) > MONTH(DATEADD(MONTH, -@MonthCount, @Date)),'OK','No') as isOK

But I am getting this syntax error:

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

enter image description here

EDIT: When I try if it is working:

DECLARE @Date DATETIME = '2015-07-31';
DECLARE @MonthCount INT = 3;
DECLARE @controlDate DATETIME = '2015-04-28';

if(MONTH(@controlDate) > MONTH(DATEADD(MONTH, -@MonthCount, @Date)))
print 'OK'
else
print 'No'

What am I doing wrong or is this a bug?

Solution

  • This happens when you're not running on a correct version of SQL Server, or if the compatibility level of the database isn't set sufficiently.

    To check compatibility level:

    select compatibility_level 
from sys.databases 
where name = '<database name>'

    To alter compatibility level:

    alter database <database-name> 
set compatibility level = 110 -- SQL Server 2012

    List of compatibility levels: https://msdn.microsoft.com/en-us/library/bb510680.aspx