using "IF" in Query

• tanehome

  Ten Centuries

  Points: 1167

  More actions

  December 14, 2010 at 12:26 am

  #99443

  Hi All,

  i'm getting error with the below query but i don't now why?

  please help.

  Query:

  SELECT dbo.tblWorkDocu.WorkID, dbo.tblWorkDocu.LastEditOn,

  IF(DATEDIFF(day, tblWorkDocu.LastEditOn, tblWorkDocu.CreatedOn)= 0,"New","Edited") AS aaa

  FROM dbo.tblWorkDocu;

  _____________________________

  the error is:

  Msg 156, Level 15, State 1, Line 1

  Incorrect syntax near the keyword 'IF'.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ','.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 14, 2010 at 1:00 am

  #1262061

  You can't use IF within a select query. Open up the SQL Books Online (press F1 while in Query Analyser) and look up CASE

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• dva2007

  SSCertifiable

  Points: 7707

  More actions

  December 31, 2010 at 1:36 am

  #1268039

  You need to use case statement instead of If.

  SELECT dbo.tblWorkDocu.WorkID, dbo.tblWorkDocu.LastEditOn,

  case when DATEDIFF(day, tblWorkDocu.LastEditOn, tblWorkDocu.CreatedOn) = 0 then 'New' else 'Edited' end as aaa

  FROM dbo.tblWorkDocu;

  Hope it helps.

• tanehome

  Ten Centuries

  Points: 1167

  More actions

  January 2, 2011 at 1:08 am

  #1268282

  Dear dva2007

  thank you very much for advice, it works v.good.