IIF()

What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew MadsenDon't believe IIF is supported in TSQL (although I think it is available to
Analysis Services...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||No IIF in SQL Server, look up CASE in Books Online.
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
CASE WHEN Closed_date_key = '19000101' THEN Current_date ELSE
Closed_date_key END)
If you used NULL instead of a token date when the closed_date_key is
"unknown", you could say
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
COALESCE(Closed_date_key, Current_date))
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||IIF is not supported in T-SQL. Use CASE:
Datediff(dd, open_date_key
, case when Closed_date_key = '1/1/1900'
then Current_Date -- I think you mean to use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew Madsen|||That would throw an error. It is just interesting it did not throw at the
IIF( instead of waiting for the '=' sign. Thanks for the help.
Andrew Madsn
Harley-Davidson Motor Company
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uf86F9B7DHA.1052@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Don't believe IIF is supported in TSQL (although I think it is available

---

to

quote:

---

> Analysis Services...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
=[QUOTE]
>

---

|||hi andrew,
Use CASE, there is no iif in SQL Server.
Try:
select datediff(dd, open_date_key, case when Closed_date_key = '19000101' th
en getdate() else Closed_date_key end)
from <table>
-Vishal|||Tom, Aaron and Vishal,
Case was it. Thank you very much. and Tom Current_Timestamp was what I
wanted even though Current_Date is a T-SQL value. Weird. Well, Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Vishal Parkar" <remove_this_vgparkar@.yahoo.co.in> wrote in message
news:673ECD20-76F2-418F-9B73-110BE31A9F8D@.microsoft.com...

quote:

---

> hi andrew,
> Use CASE, there is no iif in SQL Server.
> Try:
> select datediff(dd, open_date_key, case when Closed_date_key = '19000101'

---

then getdate() else Closed_date_key end)

quote:

---

> from <table>
> -Vishal

---