Search code examples
sqlmysql

retrieve orders over 3 calendar years

I am looking for the right query to retrieve orders that are 3 calendar years old. Example: It is October 18, 2023, and therefore I want to collect all orders from January 1, 2021 until October 18, 2023.

SELECT *
FROM ps_orders o
WHERE o.date_add >= DATE_ADD(NOW(),INTERVAL -3 YEAR);

I tried to do it like this, but it recovers 3 whole years, but I want it to be calendar.

Do you have a solution ?

Solution

  • You must use an interval for example with BETWEEN

    SELECT *
FROM ps_orders o
WHERE o.date_add BETWEEN MAKEDATE(year(DATE_ADD(NOW(),INTERVAL -3 YEAR)),1) AND NOW();

    The function MAKEDATE works like below

    SELECT MAKEDATE(year(DATE_ADD(NOW(),INTERVAL -3 YEAR)),1)

    will return

    2020-01-01