CODEWITHSUNDEEP
sql-servert-sql
Ricardo Deano
Ricardo Deano

Reputation: 2819

Append SQL table name with today's date

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.

Upvotes: 3

Views: 24789

Answers (4)

HLGEM
HLGEM

Reputation: 96590

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'

Upvotes: 0

KM.
KM.

Reputation: 103617

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

Upvotes: 6

Dan Diplo
Dan Diplo

Reputation: 25349

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))

Upvotes: 2

JonH
JonH

Reputation: 33163

DECLARE @TableName varchar(50)

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

EXEC sp_rename 'customers', @TableName

Upvotes: 1

Related Questions