What’s the equivalent syntax in MySQL for selecting date ranges by YEAR based on MSSQL syntax?

I have a MSSQL syntax to select date within 2 years:

SELECT 
   column_1
FROM
   XXX
WHERE 
   column_1 BETWEEN DATEADD(Year, -2, '2021-05-12') and '2021-05-12'

This works fine, column_1 only return date within two years of the given date.

However, I’m trying to convert it to MySQL syntax, here’s what I have tried:

SELECT 
   column_1
FROM
   XXX
WHERE 
   column_1 BETWEEN ADDDATE(date('2021-05-10'), INTERVAL 2 YEAR)

But this returns dates not only within two years range but also other years, am I missing something here? Thanks.

Answer

Your code in MS SQL is:

WHERE column_1 BETWEEN DATEADD(Year, -2, '2021-05-12') and '2021-05-12'

BETWEEN is not recommended when working with dates. In this example, May 12th may be selected twice (if column_1 is a date) or 2021-05-12 00:00:00 would be selected, if the column has a time component — but no other times for that date.

I would instead *strongly recommend phrasing this to get two complete years as:

WHERE column_1 >= DATEADD(Year, -2, '2021-05-13') AND
      column_1 < '2021-05-13'

Or in MySQL as:

WHERE column_1 >= '2021-05-13' - interval 2 year AND
      column_1 < '2021-05-13'