0

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
FROM
[IPS_TransactionDataAll1]
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')

Pseudo-code:

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

  • What SQL Engine are you using? – Daniel Björk Aug 17 '17 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 '17 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' ? – Colin 't Hart Aug 17 '17 at 19:51
10

!= 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). – MguerraTorres Aug 17 '17 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 '17 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. – MguerraTorres Aug 17 '17 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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