Share via

IIF() with Date() functions in SQL server

Anonymous
2019-04-30T12:16:22+00:00

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:

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Duane Hookom 26,820 Reputation points Volunteer Moderator
2019-04-30T14:06:49+00:00

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

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-04-30T15:44:25+00:00

    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!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-04-30T13:48:51+00:00

    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

    Was this answer helpful?

    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2019-04-30T13:25:25+00:00

    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.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-04-30T12:38:14+00:00

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

    Hope this helps

    Alan

    Was this answer helpful?

    0 comments