I need a Query to fetch records between two specific months and years also, Presently I am fetching only current year records like this

SELECT material_status, COUNT(*) c
FROM purchase_order
WHERE YEAR(date_created) = YEAR(CURDATE()) AND material_status='Closed';

But I want the records between Current year "April Month" to Upcoming year April Month" means 2016-April to 2017-April .

WHERE date_created >= CONCAT(YEAR(NOW()), '-04-01')
  AND date_created  < CONCAT(YEAR(NOW()), '-04-01') + INTERVAL 1 YEAR

This will work 'correctly' regardless of whether date_created is DATE, DATETIME, or even something with microseconds.

But it does not include any part of April for next year? Is that 'correct'?

Actually it does not make sense -- If "date_created" is always some time in the past, why reach into the future?

For April of last year through March of this year:

WHERE date_created >= CONCAT(YEAR(NOW()), '-04-01') - INTERVAL 1 YEAR
  AND date_created  < CONCAT(YEAR(NOW()), '-04-01')
  • Hey @Rick James the above query is working fine but i want to fetch record in between 2015-april to 2016 april means interval 2 years then how ? – Ranjan Sep 1 '16 at 5:16
  • I added an example -- is that what you are looking for? – Rick James Sep 2 '16 at 20:53
  • Yes but for previous year like 2015-April to 2016-April. – Ranjan Sep 3 '16 at 3:24
  • Yes I got it So many thanks , Again great job :) – Ranjan Sep 3 '16 at 11:00

Try this :

    SELECT material_status, COUNT(*) c
    FROM purchase_order
    WHERE date_created 
         between str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d')
            AND DATE_ADD(str_to_date(concat(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'),INTERVAL 1 YEAR)
     AND material_status='Closed'
     GROUP BY 1;

But: BETWEEN is inclusive. If date_created is really a DATE, then this includes 4/1/2017, but not the rest of 4/2017. Use last_day() function on to date, i.e.

last_day( str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.