IIF DATEPART syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IIF DATEPART syntax error

Author

Topic

opopanax666
Starting Member

8 Posts

Posted - 2014-03-19 : 10:49:19

Hi everyone,

quick question:

in my particular db

datepart(wk, pc0517.dbo.RESUL.CREATED)

on its own works!

datepart(wk, getdate())

on its own works!

but

IIf(datepart(wk, pc0517.dbo.RESUL.CREATED) < datepart(wk, getdate()),1,0)

gives a "syntax error near '<'"!!!

Why? Does anybody see a reason why this would not work?

TIA,
James

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-19 : 11:43:05

Don't you mean DateDiff or DateAdd?
what comes after yout IF? should be Begin/End/Else then do something??

DATEPART(minute,(CallCompletedAfter - CallAnsweredAfter))AS Minute,
DATEPART(second,(CallCompletedAfter - CallAnsweredAfter))AS Second,
DATEPART(millisecond,(CallCompletedAfter - CallAnsweredAfter))AS MilliSecond

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

If you will it you can achieve it!!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-19 : 11:47:58

IIf(datepart(wk, pc0517.dbo.RESUL.CREATED) < datepart(wk, getdate()),1,0)

I am not sure if "IFF" is a function of SQL server query. Are you using it in SQL server?

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-19 : 16:06:31

I think MIK is exactly right; the IIF function is available only in SQL 2012 and later, and if you happen to be using an earlier version, the error message you get is the error message you are seeing.

If you are on an earlier version, use a CASE expression

CASE WHEN datepart(wk, pc0517.dbo.RESUL.CREATED) < datepart(wk, getdate())
THEN 1 ELSE 0 END

opopanax666
Starting Member

8 Posts

Posted - 2014-03-20 : 05:08:18

quote:
Originally posted by James K

I think MIK is exactly right; the IIF function is available only in SQL 2012 and later, and if you happen to be using an earlier version, the error message you get is the error message you are seeing.

If you are on an earlier version, use a CASE expression
CASE WHEN datepart(wk, pc0517.dbo.RESUL.CREATED) < datepart(wk, getdate())
THEN 1 ELSE 0 END

Yep, after MIK's reply I went looking, and yes, a 2012-only feature.
Will use CASE...

Thanks y'all for your time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources