Creating Table with GetDate Function

• Brian Brant

  SSC Eights!

  Points: 839

  More actions

  April 20, 2005 at 4:51 pm

  #60343

  Is there a way to create a table with the current date as part of the table name? I tried using a create table statement using the getdate function but it didn't like that. Here's what I tried:

  create table tblTEST+'_'+getdate()

  The result was: Incorrect syntax near '+'.

  Is there a way to create a table with the date as part of the table name?

  Thanks,

  Brian

• wz700

  SSCertifiable

  Points: 7133

  More actions

  April 20, 2005 at 6:37 pm

  #553953

  try this

  declare @tbname varchar(60)

  set @tbname = 'tblTEST'+'_'+convert(varchar,getdate(),112)

  select @tbname

  exec('create table '+@tbname+'(c1 int)')

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  April 20, 2005 at 11:16 pm

  #553970

  CREATE TABLE [AA] (

   [SentDate] [smalldatetime] NULL CONSTRAINT [DF_ADS_RemitanceTransaction_SentDate] DEFAULT (getdate()))

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  April 21, 2005 at 1:42 am

  #553999

  http://www.sommarskog.se/dynamic_sql.html explains how you can do it, and also, why this is a bad idea in most cases.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Bert De Haes

  Hall of Fame

  Points: 3081

  More actions

  April 22, 2005 at 12:51 am

  #554263

  You do not need dynamic sql to do this. You can crate the table with a dummy name first, and use sp_rename to give it the name you want :

  declare @tblname sysname

  set @tblname = 'dbo.tblTEST_' + convert(varchar(8),getdate(),112)

  print @tblname

  create table dbo.__temporaryname__ ( i int not null )

  exec sp_rename 'dbo.__temporaryname__',@tblname