finding records between two dates and without entries elsewhere

I am having a problem setting up my date boundaries for the query.

I want records between 10/1/2010 and 12/31/2010, but without a record (activity) in calendar year 2011 to date.

where INV.Date_Imported BETWEEN '10/1/2010' AND '开发者_Python百科12/31/2010'
    AND INV.RecID NOT IN (
        SELECT RecID 
        FROM [VW_Invoice_All] 
        WHERE Date_Imported > '1/1/2011' 
    ) 

The only glaring issues I see is your Date_Imported line. If you want 1/1/2011 to be included in the NOT IN query, you need to change the query to

WHERE Date_Imported >= '1/1/2011'

BETWEEN is already inclusive, which is what you appear to be going for.

You don't have any times on your dates, but if they are DATETIME columns then that could be important. I would probably use:

WHERE
    INV.Date_Imported >= '10/1/2010' AND
    INV.Date_Imported < '1/1/2011' AND
    NOT EXISTS (
        SELECT *
        FROM [VW_Invoice_All] I2
        WHERE
            I2.RecID = INV.RecID AND
            I2.Date_Imported >= '1/1/2011')

(The EXISTS might give you better performance than the IN query, but test both.)

As gtcompscientist says:

BETWEEN is already inclusive...

so you only need:

WHERE INV.Date_Imported BETWEEN '2010-10-01 00:00:00' AND '2010-12-31 23:59:59'

To avoid any doubts, using the YYYY-MM-DD HH:mm:ss format means you don't need to worry about regional settings (UK dates are DD-MM-YYYY whilst US is MM-DD-YYYY but YYYY-MM-DD format is interpreted the same in both regions).

The addition of time (HH:mm:ss) ensures that you include all of 2010-12-31 i.e. from 00:00:00 to 23:59:59.

From my experience, the safest date format is 'yyyymmdd'. In the bank where I work at the moment it's the only format that works on both the production server and the test server.