Search code examples
mysqlsql-date-functionsmysql-date

Retrieve MySql Records Within a Specific Month but Less than the current year and Less than the current Day

I have a number of MySQL records with datetime records. Lets just say they are in a table called my_table and they include the following records.

ID       Date
1       2021-04-02
2       2020-04-13
3       2019-04-29
4       2018-04-30
5       2019-06-24

I want to get all records where:

  1. The month is in April (4)
  2. The year is less than the current year of 2021
  3. The day is less than the current day (The day of posting this is the 24th).

I was able to sort out the month and year part with:

SELECT * FROM my_table where date LIKE '%-4-%' AND date < '2021-04-01 00:00:00';

This will get me all records within the month of april that are not in the Current month of april. However, it also gives me all the records within the month of april even if they are greater than the current day of the 24th.

2       2020-04-13
3       2019-04-29
4       2018-04-31

What I want it to return is all previous year records within the month of april but with days less than or equal to the current day/time. In this case, the records returned should be:

2       2020-04-13

Is it possible to do this within one query?

Solution

  • Use the functions DAY(), MONTH() and YEAR() in your conditions:

    SELECT * 
FROM my_table 
WHERE MONTH(date) = MONTH(CURRENT_DATE)
  AND YEAR(date) < YEAR(CURRENT_DATE) 
  AND DAY(date) < DAY(CURRENT_DATE);

    See the demo.