Rename Mysql Table name with concat

Question

In MySQL, I need to rename a table with a suffix which identifies previous month, and I'm trying to do that with this syntax:

RENAME TABLE 'myTable' TO CONCAT('myTable',DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y_%m'));

F.e: I've table name "customers" and what I want is "customer_2015_04".

The problem is with concat order.

What am I doing wrong?

Best regards,

Answer 1

I think you need to do this using a prepared statement:

set @sql = CONCAT('RENAME TABLE myTable TO myTable',
                  DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y_%m')
                 );

prepare s from @sql;

execute s;