IIF Incorrect syntax error near error on SQL Server 2014
I am trying to control a date per month using 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 Invalid syntax near '>'
EDIT: When I try if
, it works:
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?
source to share
This happens when you are not running the correct version of SQL Server, or if the database compatibility level is not set sufficiently.
To check the compatibility level:
select compatibility_level
from sys.databases
where name = '<database name>'
To change the compatibility level:
alter database <database-name>
set compatibility level = 110 -- SQL Server 2012
Compatibility Level List: https://msdn.microsoft.com/en-us/library/bb510680.aspx
source to share
It should work correctly. You can also use CASE expression
DECLARE @Date DATETIME = '2015-07-31';
DECLARE @MonthCount INT = 3;
DECLARE @controlDate DATETIME = '2015-04-28';
SELECT
MONTH(@controlDate),
MONTH(DATEADD(MONTH, -@MonthCount, @Date)),
case when MONTH(@controlDate) >
MONTH(DATEADD(MONTH, -@MonthCount, @Date)) then 'OK' else 'No' end as isOK
source to share
I had the same problem because IIF
was introduced in SQL Server 2012, but my client was using 2008R2. Use instead case
: SQL Server 2008 IIF statement is not displayed
source to share