Question 267918 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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 ?

Tags:

2

Answers


  1. 0

    You must use an intervall 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
    Login or Signup to reply.
  2. 0

    You will need to truncate the lower bound of your date to the first of January, three years ago:

    SELECT 
  *
FROM 
  ps_orders o
WHERE 
  o.date_add >= CONCAT(YEAR(CURDATE()) - 3, '-01-01') -- Truncates the date to the first of Jan., 3 years ago. Adjust the '3', based on how many years back you need
  AND o.date_add <= CURDATE(); -- Optional, in case there are dates in the future.

    Caveat there may be a function which does this in MySQL, although I am not aware of it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search