CODEWITHSUNDEEP
sqlfunctionmysql5
ParisNakitaKejser
ParisNakitaKejser

Reputation: 14841

MySQL date_add() how to use month into this?

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.

Upvotes: 0

Views: 762

Answers (3)

Guffa
Guffa

Reputation: 700152

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;

Upvotes: 1

yu_sha
yu_sha

Reputation: 4410

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)

Upvotes: -1

Quassnoi
Quassnoi

Reputation: 425251

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

Upvotes: 1

Related Questions