<

Microsoft SQL Server 2005 Best Practices

How to datestamp a filename?

 

If you are receiving a file with the same name every day with ftp transmission, it is necessary either to delete or archive the file after processing it. This is the sample script to datestamp a filename for archiving:

 

DECLARE @SQLCommand varchar(256), @Today char(23)
SET @Today = convert(char(23), getdate(), 121)

SELECT REPLACE(@Today,':', '')
SELECT REPLACE(@Today,'.', '')
SELECT REPLACE(@Today,' ', '')

SET @SQLCommand = 'ren D:\Data\Import\Accounting\Finance.csv ' + 'Finance'+@Today + '.csv'
PRINT @SQLCommand
EXEC master.dbo.xp_cmdshell @SQLCommand

-- For a different server include UNC prefix: \\ServerName\D$ to replace D: .

-- You can also move the renamed file into an Archive folder

SET @SQLCommand = 'move D:\Data\Import\Accounting\+ 'Finance'+@Today + '.csv D:\Data\Import\Accounting\Archive\'
EXEC master.dbo.xp_cmdshell @SQLCommand



 

The World Leader in SQL Server Training
 
SQLUSA.com Home Page