IIF 'Incorrect syntax near' error at SQL Server 2014

Question

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 '>'

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?

I believe you need the `case` syntax here: http://stackoverflow.com/a/63480/60188 — Anton Gogolev, Jul 27 '15 at 09:36
@AntonGogolev but i am not want use case. IIF is more simple. — Mitat Koyuncu, Jul 27 '15 at 09:37
@Fireblade Look that => [link](http://i59.tinypic.com/5md0xz.jpg) — Mitat Koyuncu, Jul 27 '15 at 09:42
@eMKey - Yeah i see that. What's the compatibility level of your database — Pரதீப், Jul 27 '15 at 09:44

score 5 · Accepted Answer · edited Jul 27 '15 at 10:58

5

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

edited Jul 27 '15 at 10:58

marc_s

732,580
175
1,330
1,459

answered Jul 27 '15 at 09:43

thebjorn

26,297
11
96
138

My compability level is 90. For sql server 2014 what is compability level? – Mitat Koyuncu Jul 27 '15 at 09:48
Seems like 120 is the value for 2014, see link in updated answer. – thebjorn Jul 27 '15 at 09:51
I am getting error for 120 => Valid values of the database compatibility level are 80, 90, or 100. – Mitat Koyuncu Jul 27 '15 at 09:55
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) – Mitat Koyuncu Jul 27 '15 at 09:57
But at about writing sql server 2014 – Mitat Koyuncu Jul 27 '15 at 09:57
2

@thebjorn - `IIF` was introduced in `sql server 2012` – Pரதீப் Jul 27 '15 at 09:59
My bad. @Fireblade is correct. `IIF` was introduced in sqlserver 2012. You'll have to upgrade (or use `case`). – thebjorn Jul 27 '15 at 10:04
@Fireblade Thanks I will upgrade Sql server but did you see the picture on the link at before your comment? – Mitat Koyuncu Jul 27 '15 at 10:08
@eMKey - yes i saw by clicking at the ZOOM button in that page – Pரதீப் Jul 27 '15 at 10:09
1

The ssms version can be different from the database version (you can connect to a 2008R2 database from ssms 2014 like you're doing without any issues). It's the db version that is important for which sql syntax is legal though. – thebjorn Jul 27 '15 at 10:12

score 2 · Answer 2 · answered Jul 27 '15 at 09:43

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

score 1 · Answer 3 · answered Sep 07 '17 at 05:15

1

I had the same problem because IIF was introduced in SQL Server 2012 but my client used 2008R2. Use case instead: SQL Server 2008 IIF statement does not seem enabled

answered Sep 07 '17 at 05:15

Stalinko

3,319
28
31

IIF 'Incorrect syntax near' error at SQL Server 2014

3 Answers3