Append SQL table name with today's date

Question

Hello all.

I understand that I can change a sql table using the follow sp:

EXEC sp_rename 'customers', 'custs'

How would I go about appending this so that the new table has today's date as a suffix?

I've attempt variations on the below theme with little success!!

EXEC sp_rename 'customers', 'customers +(CONVERT(VARCHAR(8),GETDATE(),3))'

Any help greatly appreciated.

Answer 1

DECLARE @TableName varchar(50)

SELECT @TableName = (SELECT 'Customers_' + convert(varchar(50),GetDate(),112))

EXEC sp_rename 'customers', @TableName

Answer 2

You mean T-SQL, right? Move the functions outside of the single quotes. Something like:

EXEC sp_rename 'customers', 'customers' +(CONVERT(VARCHAR(8),GETDATE(),3))

Answer 3

This sounds like a very bad thing to do! you should evaluate your design, renaming your tables with dates in the names suggests that you will be spawning many tables, each for a different date. You could possibly add a date column into your table and use that to differentiate the data instead of creating completely new tables for different dates.

With that said, you can not have an expression as a parameter to a stored procedure in SQL Server. By attempting to concatenate the formatted date to the string 'customers', you were trying to pass an expression as a parameter.

you must store the expression in a local variable first, and then call the stored procedure with that local variable:

DECLARE @Value varchar(500)
SET @Value='customers' +(CONVERT(VARCHAR(8),GETDATE(),3))
EXEC sp_rename 'customers', @Value

Answer 4

Since you should almost never do this on such a regular basis that you need to figure out the date programmically, I suggest this for the occasional use:

EXEC sp_rename 'customers', 'customers20100408'