Comparing date fields in the same Row

• rlwilde

  SSCommitted

  Points: 1575

  More actions

  February 18, 2010 at 10:42 am

  #138936

  I'm having some trouble writing the logic on this one. What I need to do is check the dates in several fields to see if any of them are within the last two days. If they are, then I want to exclude the row.

  All of the fields are DATETIME and can be null, so I have to check for that. I want to ignore nulls, so I'm substituting the current date with GETDATE().

  This is what I have tried to use as part of my WHERE clause, but it isn't working quite right:

  AND NOT(DATEDIFF(dd,ISNULL(CLC.BILLDATE,GETDATE()),GETDATE())>2

  OR DATEDIFF(dd,ISNULL(CLC.BILLDATE2,GETDATE()),GETDATE())>2

  OR DATEDIFF(dd,ISNULL(CLC.REPORT,GETDATE()),GETDATE())>2

  OR DATEDIFF(dd,ISNULL(CLP.ENTRYDATE,GETDATE()),GETDATE())>2)

  Anyone have ideas on this, or perhaps a different approach to solving this problem?

• irobertson

  Hall of Fame

  Points: 3695

  More actions

  February 18, 2010 at 10:47 am

  #1121120

  How about

  case when DATEDIFF(dd,ISNULL(CLC.BILLDATE,GETDATE()),GETDATE())>2 then 1

  when {next date field} then 1 ... etc

  else 0

  end = 0

  Regards, Iain

• rlwilde

  SSCommitted

  Points: 1575

  More actions

  February 18, 2010 at 11:21 am

  #1121172

  I think that will do it. Thanks. 🙂