IS NOT NULL with CASE in WHERE

• texpic

  SSCertifiable

  Points: 5887

  More actions

  December 30, 2013 at 9:36 pm

  #281611

  If the record's StartDate is not today need the WHERE clause "EndDate IS NOT NULL" (all of today's records are included, even ifthey do not have an end stamp).

  The test query should return C, D, E and F.

  CREATE TABLE #Test (MyId char(1), StartDate datetime, EndDate datetime)

  --no end date

  INSERT INTO #Test (MyId, StartDate)

  SELECT 'A', '2013-10-30 10:00' UNION ALL

  SELECT 'B', '2013-11-30 10:00' UNION ALL

  SELECT 'C', '2013-12-30 10:00'

  --end date

  INSERT INTO #Test (MyId, StartDate, EndDate)

  SELECT 'D', '2013-10-30 10:00', '2013-10-30 11:00' UNION ALL

  SELECT 'E', '2013-11-30 10:00', '2013-11-30 11:00' UNION ALL

  SELECT 'F', '2013-12-30 10:00', '2013-12-30 11:00'

  SELECT * FROM #Test

  CASE WHEN StartDate < CAST(GETDATE() as Date) THEN WHERE EndDate IS NOT NULL END

• texpic

  SSCertifiable

  Points: 5887

  More actions

  December 30, 2013 at 9:43 pm

  #1676514

  Guess I'm not thinking:

  SELECT * FROM #Test

  WHERE (StartDate < CAST(GETDATE() as Date) AND EndDate IS NOT NULL)

  OR StartDate >= CAST(GETDATE() as Date)