CODEWITHSUNDEEP
sqlsql-server
NaveedAnjum
NaveedAnjum

Reputation: 3

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 '12/31/2010'
    AND INV.RecID NOT IN (
        SELECT RecID 
        FROM [VW_Invoice_All] 
        WHERE Date_Imported > '1/1/2011' 
    )

Upvotes: 0

Views: 121

Answers (4)

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 0

Kevin Hogg
Kevin Hogg

Reputation: 1781

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.

Upvotes: 0

Tom H
Tom H

Reputation: 47444

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.)

Upvotes: 0

gtcompscientist
gtcompscientist

Reputation: 681

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.

Upvotes: 2

Related Questions