0

I am creating a Stored Procedure to get all the records from the previous 4 months, given the month and year variables.

The datetime format of etimestamp column is 12/30/2020 12:000:00 AM.

If inMonth >= 4, this statement will work:

SELECT * FROM table_name
WHERE Month(etimestamp) in (inMonth , inMonth - 1, inMonth - 2, inMonth - 3) AND
Year(etimestamp) = inYear

However, this won't work if inMonth is not >= 4.

I want a query that will also work for the last year.

For example: inMonth = 2 AND inYear = 2020

Expected Output Records:

  • February 2020
  • January 2020
  • December 2019
  • November 2019
Jonnel VeXuZ Dorotan
  • 418
  • 5
  • 15

2 Answers2

1

You can try this

SELECT * FROM table_name WHERE etimestamp between now() - INTERVAL 4 MONTH and now()
Mr Syhd
  • 73
  • 4
  • 1
    and you can change now() to STR_TO_DATE('01,imonth,inyear','%d,%m,%Y') to get data within the previous 4 months of the variable inyear and inmonth – Mr Syhd Aug 31 '21 at 02:51
  • Thank you so much, @Mr Syhd. I obtained the idea to get started. I added modifications to make clause **inclusive**. This is my final query: `SELECT * FROM table_name WHERE (STR_TO_DATE('01, inMonth, inYear','%d, %m, %Y') - INTERVAL 3 MONTH) <= etimestamp AND etimestamp <= (STR_TO_DATE('01, inMonth, inYear', '%d, %m, %Y') + INTERVAL 1 MONTH)` – Jonnel VeXuZ Dorotan Aug 31 '21 at 03:45
  • @JonnelVeXuZDorotan your welcome, yeah you're right, i forgot about the inclusiveness – Mr Syhd Aug 31 '21 at 04:34
0

Thank you so much, @Mr Syhd for the answer. I obtained the idea to get started. I added modifications to your answer to make the clause inclusive.

This is now my final query:

SELECT
    *
FROM
    table_name
WHERE
    (STR_TO_DATE(CONCAT('01, ', inMonth, ', ', inYear),'%d, %m, %Y') - INTERVAL 3 MONTH) <= etimestamp AND
    etimestamp <= (STR_TO_DATE(CONCAT('01, ', inMonth, ', ', inYear),'%d, %m, %Y') + INTERVAL 1 MONTH)

Helpful Link: MySQL “between” clause not inclusive?

Jonnel VeXuZ Dorotan
  • 418
  • 5
  • 15