declare
 @sql varchar(500), @name varchar(100);
 
set
 @name = (select 'sov01_usg'
+ '' +
case 
when convert(varchar(10),getdate(),103)< '%15%' then '1'
else '2'
end+ '' +
'1'
+ '' +SUBSTRING(convert(varchar(8),getdate(),1),7,2)
+ '' +
case 
	when SUBSTRING(convert(varchar(8),getdate(),112),7,2)< '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'01'
	when SUBSTRING(convert(varchar(8),getdate(),112),7,2)>= '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'15'
end)

/****** Object:  Table [dbo].[Usage]    Script Date: 02/11/2013 20:21:35 ******/
select
@sql = 'CREATE TABLE ' + @name + '.dbo.Usage(
	[UsageID] [int] NOT NULL,
	[BatchID] [int] NOT NULL,
	[RecordNumber] [int] NOT NULL,
	[InstanceNumber] [tinyint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UsageTypeID] [smallint] NOT NULL,
	[CustID] [int] NOT NULL,
	[UsgSvcID] [int] NULL,
	[ProcessingFlags] [int] NULL,
	[CallStartTime] [datetime] NOT NULL,
	[ServiceNumber] [varchar](20) NOT NULL,
	[AcctCode] [varchar](10) NULL,
	[Duration] [int] NOT NULL,
	[ProgramID] [smallint] NOT NULL,
	[DistanceID] [tinyint] NULL,
	[Cost] [money] NULL,
	[Charge] [money] NULL,
	[OrigPlace] [varchar](16) NULL,
	[OrigLata] [smallint] NULL,
	[OrigNPA] [smallint] NULL,
	[OrigState] [varchar](2) NULL,
	[OrigCountryCode] [smallint] NULL,
	[OrigNumber] [varchar](16) NULL,
	[OrigOCN] [varchar](4) NULL,
	[TermPlace] [varchar](16) NULL,
	[TermLata] [smallint] NULL,
	[TermNPA] [smallint] NULL,
	[TermState] [varchar](2) NULL,
	[TermCountryCode] [smallint] NULL,
	[TermNumber] [varchar](16) NULL,
	[TermOCN] [varchar](4) NULL,
	[RatePeriod] [varchar](1) NULL,
	[ActualDuration] [int] NOT NULL,
	[RatePlanDetailID] [int] NULL,
	[Text1] [varchar](50) NULL,
	[OrigNetworkElementID] [varchar](5) NULL,
	[TermNetworkElementID] [varchar](5) NULL,
	[PreDiscountDuration] [int] NULL,
	[PreDiscountCharge] [money] NULL,
	[Surcharge] [money] NULL
) ON [PRIMARY]'

print (@sql)
---exec(@sql)

Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ','.

declare
 @sql varchar(MAX), @name varchar(100);
....