Subtract month and day mysql

What's happening under the hood?

Dealing with different lengths of months

When you use DATE_SUB(), MySQL thinks about each month's length. For example, if you tried to subtract a month from January 31, MySQL would return December 31, since it took into account that February doesn't have 31 days.

// Let's go back to the future... uh, past.
SELECT DATE_SUB('2021-01-31', INTERVAL 1 MONTH);
-- Result: 2020-12-31


Leap Years are no problem

If you're dealing with a leap year and subtract a month from March 1, MySQL will return February 1, not February 29th. How can one SQL function be so smart?

// To leap or not to leap, that's the SQL question.
SELECT DATE_SUB('2024-03-01', INTERVAL 1 MONTH);
-- Result: 2024-02-01


Time doesn't remain still

In some cases you might need to consider the time as well as the date, in which case you should use NOW(). However, if you only care about the date, stick with CURDATE().

// Marty, where we're going, we don't need... wait, yes we do!
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH + INTERVAL 4 DAY);


Using the other tools in your SQL toolbox

Subtract without subtraction? Learn this one trick!

Did you know you can use the DATE_ADD() function to subtract from a date? Simply use a negative interval. Opticians hate this one weird trick!

// I'm positive we need to go negative. Wait, what?
SELECT DATE_ADD(CURDATE(), INTERVAL -1 MONTH), DATE_ADD(CURDATE(), INTERVAL -4 DAY)


A little extra subtraction

For those occasions when subtracting one interval is not enough, you can use nested DATE_ADD functions.

// Inception! We need to go DEEPER!
SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL -1 MONTH), INTERVAL -4 DAY);


Avoiding invalid dates

Sometimes when subtracting, you can end up with a date that doesn't exist. With DATE_SUB() MySQL has got you covered:

// How many days in February? 
SELECT DATE_SUB('2021-03-31', INTERVAL 1 MONTH + INTERVAL 4 DAY);
-- Result: 2021-02-25


Pitfalls to avoid

Corner cases

When dealing with months like February, or leap years, you should brace for some surprise corners MySQL can turn. Embrace the turns to ensure your SQL stays tight.

Considering compatibility

Before you go all "guns blazing" with these functions, make sure to check the MySQL version you're using to ensure compatibility.

The question of performance

Do bear in mind the performance implications for large datasets. Indexing or caching may be needed when using such functions in high-load environments.