I have a query where I want to exclude rows if a date column has any one of a set of dates.

I am having trouble figuring how to return the correct result sets without filtering one month at a time.

My initial attempts assumed the operator '!=' would be able to specify multiple variables, but I might be mistaken.

How do I filter the results in such a way that removes all the specified dates I do not want?

SELECT * INTO IPS_TransactionDataAll2
where Format([Start Date], 'MM/yyyy') != ('07/2016''08/2016''09/2016''10/2016''11/2016''10/2016''01/2017''02/2017''03/2017''04/2017''05/2017''06/2017''07/2017')


Select into TableB from TableA Where dates not in the months 7-11/2016 and 1-7/2017

  • What SQL Engine are you using? Aug 17, 2017 at 17:03
  • @DanielBjörk The original screenshot that I replaced with text was of (MS) SSMS, so I've added a [sql-server] tag.
    – BradC
    Aug 17, 2017 at 17:59
  • Why don't you do use a where clause something like WHERE [Start Date] NOT BETWEEN DATE '2016-07-01' AND DATE '2017-07-31' ? Aug 17, 2017 at 19:51

1 Answer 1


!= doesn't support a list of items like that.

Try one of these (note the commas between items):

WHERE Format([Start Date], 'MM/yyyy') NOT IN ('07/2016','08/2016','09/2016',...)

or even better (and faster), without converting to text (if your date range is truly consecutive like your example):

WHERE [START Date] NOT BETWEEN '2016-07-01' AND '2017-07-31 23:59:59'

Since between is inclusive of the endpoints, that is equivalent to:

WHERE [START Date] >= '2016-07-01' 
AND   [START Date] <  '2017-08-01'
  • Might want to do '2017-07-31 23:59:59' in the BETWEEN where clause, as any records at midnight (8/1/17) would be excluded (Especially if there is functionality that truncates time off).
    – SQLDevDBA
    Aug 17, 2017 at 18:26
  • 1
    @MguerraTorres Yep, that's what I was suggesting with the "endpoints" comment, but I'll include your suggestion.
    – BradC
    Aug 17, 2017 at 18:37
  • My apologies! I only focused on the BETWEEN segment as it's my "go-to" and the first approach I'd take.
    – SQLDevDBA
    Aug 17, 2017 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.