CODEWITHSUNDEEP
mysql
user6132703
user6132703

Reputation:

How do I find all data from a table in a specific month

I am trying to retrieve all data from table in a specific month, my date format in the database table is 2016-06-19 I am need to find with the respect of a selected date, like if I select 2016-05-22 it will retrieve 2016-05-07 to 2016-06-06 interval values from the table.

And also to all the values in this month which is 05 month, I am using mysqli with PHP. I have tried using this query 

SELECT * FROM t_tenancy_details WHERE
    agreement_date >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )AND
    agreement_date < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )

This works fine for the running month, But problem is that if I select a previous month it does not work.

Upvotes: 1

Views: 117

Answers (2)

Sourabh
Sourabh

Reputation: 429

It's not very clear from the question as to what is needed. Based on the 2 requirements that I could understand:

  1. Get everything surrounding the given date (i.e. +-15 days from the given date):

    SELECT * 
  FROM t_tenancy_details 
 WHERE agreement_date >= DATE_SUB(@d, INTERVAL 15 DAY)
   AND agreement_date <= DATE_ADD(@d, INTERVAL 15 DAY);

  2. Get all the records where the month is same as the month in the given date:

    SELECT *
  FROM t_tenancy_details
 WHERE MONTH(agreement_date) = MONTH(@d)
   AND YEAR(agreement_date) = YEAR(@d);

where @d is the input date, in your case - CURRENT_DATE

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

Should be this 

SELECT * FROM t_tenancy_details
WHERE (YEAR(t_tenancy_details) => YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(agreement_date) => MONTH(CURRENT_DATE - INTERVAL 1 MONTH))
AND (YEAR(t_tenancy_details) <= YEAR(CURRENT_DATE)
AND MONTH(agreement_date) <=  MONTH(CURRENT_DATE))

Upvotes: 0

Related Questions