MySQL date_add() how to use month into this?

Question

Hallo all, i have this SQL

SELECT DATE_ADD( '2009-'+ MONTH( NOW() ) +'-01' , INTERVAL -1 MONTH );

i can't get it to work, what i make wrong here?

tanks for help.

Answer 1

SELECT CONCAT_WS('-', '2009', MONTH(NOW()), '01') - INTERVAL 1 MONTH

Answer 2

Plus is an arithmetical operator, you have to use concat.

SELECT DATE_ADD( concat('2009-',MONTH(NOW()),'-01') , INTERVAL -1 MONTH )

or better

select date(now()) -  interval day(NOW())-1 day - interval 1 month;

(this will also work in 2010)

Answer 3

It's the concatenation of the date that doesn't work. It converts the strings to numbers, so you get 2009+11+-1 = 2019, which then fails to convert to a date.

Instead of concatenating a date from strings, you can use the last_day function to get the last day of the current month, add one day to get to the next day of the next month, then subtract two months to get to the first day of the previous month:

select last_day(now()) + interval 1 day - interval 2 month;