appropriate date format

Question

appropriate date format

sqlstar2011

SSCertifiable

Points: 6000
More actions
March 15, 2010 at 8:43 am

#221735

I use following query to rename a table
DECLARE @RenameTableDetail varchar(50)
SELECT @RenameTableDetail = Replace(('WTable' + CAST ( GetDate() AS varchar (50 ))), ' ', '_')
print @RenameTableDetail
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'WTable')
EXEC sp_rename @objname = 'WTable', @newname = @RenameTableDetail
GO
and table is renamed from WTable to WTableMar_15_2010_10:39AM
I would like to remove the timestamp and the '_' from between Mar_15_2010. So the end result name would like:
WTableMar152010
thoughts?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

shahab-205604 (3/15/2010)
I use following query to rename a table
DECLARE @RenameTableDetail varchar(50)
SELECT @RenameTableDetail = Replace(('WTable' + CAST ( GetDate() AS varchar (50 ))), ' ', '_')
print @RenameTableDetail
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'WTable')
EXEC sp_rename @objname = 'WTable', @newname = @RenameTableDetail
GO
and table is renamed from WTable to WTableMar_15_2010_10:39AM
I would like to remove the timestamp and the '_' from between Mar_15_2010. So the end result name would like:
WTableMar152010
thoughts?

You could try replacing you CAST statement with something like:

REPLACE(REPLACE(RTRIM(CONVERT(VARCHAR(12), GETDATE())), ' ', ' '), ' ', '_')

I'd rather use a different date format:

CONVERT(VARCHAR(8), GETDATE(), 112)

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I recommend the replace option that was provided.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events