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...
> 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...
> 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
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
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
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
IIF is not supported in T-SQL. =Use CASE:
Datediff(dd, =open_date_key
, case when Closed_date_key =3D ='1/1/1900'
then Current_Date -- I think you mean to =use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Andrew Madsen" wrote in message news:uU5u73B7DHA.632@.T=K2MSFTNGP12.phx.gbl...What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =3D'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 1Line 1: Incorrect syntax near ='=3D'.TIAAndrew Madsen
--=_NextPart_000_0289_01C3EBF6.C5A01230--|||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...
> Don'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...
> > 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
> >
> >
>|||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
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...
> 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)
> from <table>
> -Vishal
No comments:
Post a Comment