[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql select for records in this month only

Posted on 2012-09-15
9
Medium Priority
?
320 Views
Last Modified: 2012-09-16
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

0
Comment
Question by:Luey
  • 4
  • 3
  • 2
9 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38402403
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())
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38402418
To allow indexes to work, checking a range works best like
SELECT * 
FROM views 
WHERE vew_month >= STR_TO_DATE(concat('01-',month(CURDATE()),'-',year(CURDATE())),'%d-%m-%Y')
AND vew_month < STR_TO_DATE(concat('01-',month(date_add(CURDATE(), interval 1 month)),'-',year(date_add(CURDATE(), interval 1 month))),'%d-%m-%Y')

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 38402547
@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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Luey
ID: 38403823
it is stored as a "date"  2012-09-15
0
 

Author Comment

by:Luey
ID: 38403843
@ lwadwell  do I need to change anything in you code due to the way my date is?
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38403851
If the 'vew_month' column is actually a date/datetime datatype ... no, you shouldn't need to.
0
 

Author Closing Comment

by:Luey
ID: 38403862
It works even but I do not understand is.  I guess it gives me something to study on . Thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38403922
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).
0
 

Author Comment

by:Luey
ID: 38403945
THanks a whole lot for that explanation.  Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 10 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question