SQL Query for Month

  • Thread starter Thread starter Brandon
  • Start date Start date
B

Brandon

I need a SQL statement that will return all records with a month in the
"Date" column that is 1 month past the current month. For example, since
this is September (month 9), the query will return all records where the
month is October (month 10) from the date column that is formatted as
MM/DD/YYYY. I'm getting close, but I can't quite figure out how to extract
only the month from the current date. Any ideas?

--
Brandon

==================
Presentations Direct - http://www.presentationsdirect.com
Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
Paper Shredders
Click to expand...
http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
==================
 
Brandon

Month([YourDateField]) gives you the month number.

Is [YourDateField] a Date/Time data type? The format is just how it gets
displayed, not the underlying data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I need a SQL statement that will return all records with a month in the
"Date" column that is 1 month past the current month. For example, since
this is September (month 9), the query will return all records where the
month is October (month 10) from the date column that is formatted as
MM/DD/YYYY. I'm getting close, but I can't quite figure out how to extract
only the month from the current date. Any ideas?
Click to expand...

Try a criterion of
= DateSerial(Year(Date()), Month(Date()) + 1, 1) AND < DateSerial(Year(Date()), Month(Date()) + 2, 1)
Click to expand...


John W. Vinson[MVP]
 
Thank, this gives me some ideas, but I'll have to tweak some to make it work
in December. Because as it looks right now, this would not work in December
because the next year would not be < the current year.

--
Brandon

==================
Presentations Direct - http://www.presentationsdirect.com
Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
Paper Shredders
Click to expand...
http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
==================
 
Thank, this gives me some ideas, but I'll have to tweak some to make it work
in December. Because as it looks right now, this would not work in December
because the next year would not be < the current year.
Click to expand...

Actually it will work fine. If you run the query in December 2006, it
will get the range January 1 2007 through January 31 2007. Passing 13
as the month to DateSerial will be interpreted correctly - the first
month of the next year.

John W. Vinson[MVP]
 
Back
Top