File name as yesterday's date

I'm running a DTS daily that needs to create a file with a unique name, as
yesterday's date. (Ex: 20050223)
I figured out how to get the date part of it:
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
but I can't seem to get it to create a table with that unique name each day.
Any ideas?
CREATE TABLE 'Date'
Ed>> I'm running a DTS daily that needs to create a file [sic] with a
unique name, as yesterday's date. (Ex: 2005-02-23) <<
No. no. no.
1) You have just re-discovered the old IBM tape system management
convention, which used a "yyddd' numbering scheme on the tape labels.
It was one of the thing that blew up in the Y2K disaster. You are over
50 years behind current IT standard practices.
2) We do not alter a schema by adding new tables without a good
reason. Altering a schema on a daily basis is sure sign of a major
design flaw.
3) This design flaw is called attribute splitting. you have taken the
value of a temporal attribute (date of creation) and split it into
separate tables. Would you keep a personnel table in two parts, male
and female employees? Of course not. You need one table with a date
column in it.|||Create an os file or a table?
***** OS File *****
Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?292
***** TABLE *****
Use dynamic sql.
Example:
declare @.tn sysname
declare @.sql nvarchar(4000)
set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
execute sp_executesql @.sql
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Edo" wrote:

> I'm running a DTS daily that needs to create a file with a unique name, as
> yesterday's date. (Ex: 20050223)
> I figured out how to get the date part of it:
> CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
> but I can't seem to get it to create a table with that unique name each da
y.
> Any ideas?
> CREATE TABLE 'Date'
> Ed|||Thanks for your help on that matter.
But how would this apply to inserting, especially when appling sums and
inner joins.
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
Select COLMAT_TRACK_EMPS.EMPLOYEE_NAME,
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'H' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'H',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'M' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'M',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'P' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'P',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'A' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'A',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'V' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'V',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'L' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'L',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'S' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'S',
SUM (TRACK_DETAIL_CI.QTY) AS QTYSUM
FROM COLMAT_TRACK_EMPS INNER JOIN
TRACK_DETAIL_CI ON COLMAT_TRACK_EMPS.EMPLOYEE_NO =
TRACK_DETAIL_CI.EMPLOYEE_NO
INNER JOIN ITEM_MAST ON ITEM_MAST.EDP_NO = TRACK_DETAIL_CI.EDP_NO
WHERE TRACK_DETAIL_CI.TRANS_DATE =
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
GROUP BY COLMAT_TRACK_EMPS.EMPLOYEE_NAME
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
SELECT 'TOTAL' AS EMPLOYEE_NAME, SUM (EDS_TRIAL.H), SUM (EDS_TRIAL.M), SUM
(EDS_TRIAL.P), SUM (EDS_TRIAL.A), SUM (EDS_TRIAL.V), SUM (EDS_TRIAL.L), SUM
(EDS_TRIAL.S), SUM (EDS_TRIAL.QTYSUM)
FROM CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
"Alejandro Mesa" wrote:
> Create an os file or a table?
> ***** OS File *****
> Working with files and the FileSystemObject
> http://www.sqldts.com/default.aspx?292
> ***** TABLE *****
> Use dynamic sql.
> Example:
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
> set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
> execute sp_executesql @.sql
> go
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
> "Edo" wrote:
>