asked on

sql select for records in this month only

How can you select for records in mysql database for the current month only.

"SELECT * FROM views WHERE vew_month = the current month only"

Open in new window

_agx_

If "vew_month" stores a number 1-12, you can use:

WHERE vew_month = month(curDate())

That returns all records for September ie month = 9. If you only want records for September 2012, you'll need a year filter too:

WHERE vew_month = month(curDate())
AND vew_year = year(curDate())

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

@Luey - Hmm.. lwadwell raises a good question. What is the data type of "vew_month"? The name implies it stores a month number but it could also be a date/time.

If "vew_month" contains a month number ie 1-12, use my suggestion. If it's a date/time (ie mm/dd/yyyy or mm/dd/yyyy hh:mm:ss) use lwadwell's suggestion.

Luey

ASKER

it is stored as a "date" 2012-09-15

Luey

ASKER

@ lwadwell do I need to change anything in you code due to the way my date is?

Lee Wadwell

If the 'vew_month' column is actually a date/datetime datatype ... no, you shouldn't need to.

Luey

ASKER

It works even but I do not understand is. I guess it gives me something to study on . Thanks

Lee Wadwell

STR_TO_DATE(concat('01-',month(CURDATE()),'-',year(CURDATE())),'%d-%m-%Y')

CURDATE() - returns the date today (e.g. 2012-09-17)
month() - extracts the month number from a date (e.g. 9)
year() - extracts the year from a date (e.g. 2012)
concat() - concatenates multiple values into one string (e.g. '01-9-2012')
STR_TO_DATE() - turns a string into a date value/datatype.

date_add() adds an defined interval to a date (e.g. 2012-08-17 becomes 2012-09-17).

Luey

ASKER

THanks a whole lot for that explanation. Thanks