select query for only prior month records 1

[nmc1975]

hi, how do I set my query criteria in a date field to select only records for prior month data? for example: it is november 5th today, and I want only records from oct.01 through oct. 31st?

thanks in advance,
norman

 

[tlbroadbent]

Here is one way to do search for last month's records.

Select * from tableA
Where Year(DateColumn)=Year(date)
And Month(dateColumn)=Month(date)-1 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

I attempted this and received errors. I continued my search (As I needed the same question answered) and was able to compose this which worked for me. Maybe I'm just an idiot, but I'm new to SQL, and I need things spelled out for me.

SELECT *
FROM tableA
WHERE Year(DateColumn) = Year(Date())
And Month(DateColumn) = Month(date())-1

it's that extra set of () that I was missing to have my SQL run properly.

 

[tlbroadbent]

The parenteheses after Date are necessary because it is a function. Sorry I left them off my post. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[nmc1975]

this is great! thanks for your help tlbroadbent and Yosemite Sam.

norman

 

[Guest_imported]

Thanks for the post! Even I can use this! Thanks!

 

[Guest_imported]

I just found out that this has problems with the increment of a new year. Since it is Jan 2002 it will go back 3 months, but it cannot go back to Oct Nov Dec of 2001. Any ideas on how to adjust this to take into consideration the changes of the year?
Thank you.

 

[johpje]

you could try something like:

WHERE format(DateColumn, &quot;yyyy mm&quot < &quot;2002 01&quot; AND format(DateColumn, &quot;yyyy mm&quot >= &quot;2001 10&quot;

This should return any date from 1th of october to 31th of december 2001.

PS: I used it alot, but always in a HAVING clause, and i'm running linux at the moment, so I wasn't able to test it. But i'm pretty sure it works for a WHERE clause too.

regards
Johpje