IIF() with Date() functions in SQL server

Question

Hello there, I am trying to use the IIF function with Date() in SQL server as I used in Access Database to create a calculated column, but it doesn't work. Any help will be much appreciated. Thank you in advance! 

In Access form I have a column named [Target Days] =IIf([Completion Date] Is Null And DateDiff("d",[Received Date],Date())>0,DateDiff("d",[Received Date],Date()),DateDiff("d",[Received Date],[Completion Date]))

The above code works perfectly in Access Database. Now I want to create the same calculated column 'Target Days' in SQL server query. Below is the result. 

 

Created a column in SQL server table and gives an error like this:

Answer 1

You can try something like which is accepted but I'm not sure I got the logic correct:

CASE WHEN [Completion Date] Is Null And DateDiff(Day,[Received Date],GetDate())>0 THEN DateDiff(Day,[Received Date],GetDate()) ELSE DateDiff(Day,[Received Date],[Completion Date]) END

Answer 2

You can try something like which is accepted but I'm not sure I got the logic correct:

CASE WHEN [Completion Date] Is Null And DateDiff(Day,[Received Date],GetDate())>0 THEN DateDiff(Day,[Received Date],GetDate()) ELSE DateDiff(Day,[Received Date],[Completion Date]) END

Hi Duane, it worked perfect both in SQL server query and table. Thanks a lot for help!

Answer 3

Alan is right: SQL Server does not know anything about VBA functions. That means your Date() function will also not work, and neither will DateDiff - SQL has it, but using a slightly different syntax.

Thank you Alan and Tom. I was thinking the code would also work in SQL as this worked in Access. I am not so good in SQL server, so would love to know how to accomplish this in SQL server.  Tom, would you mind letting me know the syntax, please? 

Thanks

Answer 4

Alan is right: SQL Server does not know anything about VBA functions. That means your Date() function will also not work, and neither will DateDiff - SQL has it, but using a slightly different syntax.

Answer 5

IIF does not work in SQL Server. Use IF , or Case/When

Hope this helps

Alan