Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Subtract month and day mysql

Tags:

sql

mysql

I need to subtract 1 month and 4 days with mysql, I saw the command DATE_ADD (NOW (), - 1 MONTH) perfect for 1 month but for 1 month and 4 days, using 31 days is not valid for every month that some bring 30, 29, 28. I can not add 31 + 4, 30 + 4, etc.

like image 760
Alexd2 Avatar asked Sep 27 '11 11:09

Alexd2


People also ask

Can I subtract dates in MySQL?

Definition and Usage The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.

How do I subtract a day from a date in SQL?

Following the answer from Philip Rego, you can use SELECT GETDATE() - 1 to subtract days from a date.

What is Date_add in MySQL?

The DATE_ADD() function adds a time/date interval to a date and then returns the date.


4 Answers

using DATE_SUB [docs] like :

DATE_SUB((DATE_SUB(curdate(), INTERVAL 1 MONTH)), INTERVAL 4 DAY)
like image 83
Haim Evgi Avatar answered Oct 09 '22 19:10

Haim Evgi


SELECT DATE_ADD(DATE_ADD(NOW(),INTERVAL -1 MONTH), INTERVAL -4 DAY)
like image 23
ptomli Avatar answered Oct 09 '22 20:10

ptomli


Keep it simple:

SELECT CURDATE() - INTERVAL 1 MONTH - INTERVAL 4 DAY;

or

SELECT '2014-03-27' - INTERVAL 1 MONTH - INTERVAL 4 DAY;

or if you like to preserve the current time:

SELECT NOW() - INTERVAL 1 MONTH - INTERVAL 4 DAY;

(Tested on MySQL 5.1.73 and newer)

like image 31
david Avatar answered Oct 09 '22 21:10

david


SELECT CURRENT_TIMESTAMP + INTERVAL - 1 MONTH + INTERVAL - 4 DAY;
or
SELECT CURRENT_DATE + INTERVAL - 1 MONTH + INTERVAL - 4 DAY;

like image 30
M Rostami Avatar answered Oct 09 '22 21:10

M Rostami