How to strip out month from date?

[Tom]

What I really want to do is find all records where one field's value is one month and another field's value is a different month,
and strip out all the records where both fields are in the same month.

 

[Guest]

Hi Tom,

Try the following query in the sample Northwind database (Northwind.mdb).
You likely have a copy installed on your hard drive already. Create a new
query. Dismiss the Add Tables dialog without adding any tables. In query
design view, click on View > SQL View. You should see the word SELECT
highlighted. Copy the following SQL statement (Ctrl C) and paste it into the
SQL view (Ctrl V), replacing the SELECT keyword:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate
FROM Orders
WHERE Month([OrderDate])=Month([RequiredDate])=0;


This query should return 738 orders in an unmodified copy of Northwind.
Without the criteria, there are 830 orders returned. However, this SQL
statement will filter out records where the year is not the same, which may
or may not be what you desire. If you want to filter out records where the
month and year are the same, then use this variation instead:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate
FROM Orders
WHERE Month([OrderDate]) & Year([OrderDate])
=Month([RequiredDate]) & Year([RequiredDate])=0;


738 records are still returned in this case. However, if you now change the
RequiredDate for OrderID 10248 to 01-Jul-1997, you should see that the first
version drops one record (737 records) while the second version does not.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Baz]

What I really want to do is find all records where one field's value is

one month and another field's value is a different month,

and strip out all the records where both fields are in the same month.

Sounds like you want something like this:

SELECT * FROM some_table WHERE Month(some_date) <> Month(some_other_date)