Creating Table in Stored Proc With Phrase and Today's Date

Question

Post reply

Creating Table in Stored Proc With Phrase and Today's Date

Roger Abram

Hall of Fame

Points: 3683
More actions
September 2, 2010 at 12:31 pm

#237776

I'm trying to write a stored proc and when it runs, it will use today's date as part of the name of a table it will create when it run each time. I can't seem to figure it out.
I want the table name to be "Name_S_YYYYMMDD" and this is what I have so far:
Create table 'Name_S_'+RIGHT('0000'+ convert(varchar(4),YEAR(getdate())),4)
+RIGHT('00' + convert(varchar(2),MONTH(getdate())),2)
+RIGHT('00' + convert(varchar(2),DAY(getdate())),2)
([AccessKey] [uniqueidentifier] NOT NULL ,
[Grantee] [uniqueidentifier] NOT NULL ,
[Permission] [int] NOT NULL ,
[GroupKey] [uniqueidentifier] NULL ,
[RoleKey] [uniqueidentifier] NULL ,
[UserKey] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
Any help would be appreciated.
Thank you.
Rog

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Will this help

Declare @date Char(10)

set @date = convert(char(10),'2010-09-02 14:56:09.840',101)

PRINT @date

Using GETDATE()

Declare @date Char(10)

set @date = convert(char(10),GETDATE(),101)

SELECT @date

Result will be 09/02/2010 in either case, or can convert to VARCHAR and it works

Declare @date VARCHAR(10)

set @date = REPLACE(convert(VARCHAR(10),GETDATE(),101),'/',''

Above reults: 09022010

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Roger Abram Hall of Fame Points: 3683 More actions · Answer 2

I tried using a variable to create the table but that didn't seem to work:

create table @variable (fields...)

WayneS SSC Guru Points: 95460 More actions · Answer 3

The table name can't be a variable in the CREATE TABLE statement, so you'll have to use dynamic sql:

DECLARE @sql varchar(max);

SET @sql = 'CREATE TABLE dbo.' + QuoteName(convert(char(10),'2010-09-02 14:56:09.840',101)) + ' (ColA int....)';

EXEC (@sql);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Also, the format that you're looking for in the CONVERT is 112, which gives you YYYYMMDD without the need to use REPLACE to get rid of extraneous characters.

SELECT Convert(varchar(8), Getdate(), 112)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

I tested this and it did work (SQL 2005)

DECLARE @Table NVARCHAR(1000)

SET @Table =

'CREATE TABLE [dbo].[filestats](

[statskey] [int] IDENTITY(1,1) NOT NULL,

[dbname] [varchar](128) NULL,

[fName] [varchar](2048) NULL,

[timeStart] [datetime] NULL,

[readsNum1] [bigint] NULL,

[readsBytes1] [bigint] NULL,

[readsIoStall1] [bigint] NULL,

[writesNum1] [bigint] NULL,

[writesBytes1] [bigint] NULL,

[ioStall1] [bigint] NULL

)'

EXECUTE sp_executesql @Table

Specifically what error message(s) are you getting?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Roger Abram Hall of Fame Points: 3683 More actions · Answer 6

Thanks everybody so far... here is what I have that is generating a syntax error.

What I am trying to accomplish... dynamically create the table name with today's date... so today's table would be Name_S_20100902... and then create the fields as shown below. I get an Incorrect syntax near 'AccessKey'. error message so it has something to do with trying to dynamically create the table name and then the fields.

DECLARE @create nvarchar(1000)

SET @create = 'CREATE TABLE dbo.Name_S_'+Convert(varchar(8), Getdate(), 112)

(

[AccessKey] [uniqueidentifier] NOT NULL ,

[Grantee] [uniqueidentifier] NOT NULL ,

[Permission] [int] NOT NULL ,

[GroupKey] [uniqueidentifier] NULL ,

[RoleKey] [uniqueidentifier] NULL ,

[UserKey] [uniqueidentifier] NULL

)

EXECUTE sp_executesql @create

Nevyn SSChampion Points: 14210 More actions · Answer 7

Roger Abram (9/2/2010)
Thanks everybody so far... here is what I have that is generating a syntax error.
What I am trying to accomplish... dynamically create the table name with today's date... so today's table would be Name_S_20100902... and then create the fields as shown below. I get an Incorrect syntax near 'AccessKey'. error message so it has something to do with trying to dynamically create the table name and then the fields.
DECLARE @create nvarchar(1000)
SET @create = 'CREATE TABLE dbo.Name_S_'+Convert(varchar(8), Getdate(), 112)
(
[AccessKey] [uniqueidentifier] NOT NULL ,
[Grantee] [uniqueidentifier] NOT NULL ,
[Permission] [int] NOT NULL ,
[GroupKey] [uniqueidentifier] NULL ,
[RoleKey] [uniqueidentifier] NULL ,
[UserKey] [uniqueidentifier] NULL
)
EXECUTE sp_executesql @create

You're missing some quotes. Remember, you're building a string and then executing it.

DECLARE @create nvarchar(1000)

SET @create = 'CREATE TABLE dbo.Name_S_'+Convert(varchar(8), Getdate(), 112)+'

(

[AccessKey] [uniqueidentifier] NOT NULL ,

[Grantee] [uniqueidentifier] NOT NULL ,

[Permission] [int] NOT NULL ,

[GroupKey] [uniqueidentifier] NULL ,

[RoleKey] [uniqueidentifier] NULL ,

[UserKey] [uniqueidentifier] NULL

)'

EXECUTE sp_executesql @create

Roger Abram Hall of Fame Points: 3683 More actions · Answer 8

Thanks! I kept playing around with where to place them and just didn't get the right combination.

Thank you and thank everyone who jumped in.

Roger