Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is...

EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-timestamp.csv" -T -c -t, '

As you can see, I need the 'timestamp' to be replaced with the actual timestamp, and for the format to be .csv, how can I achieve this?

share|improve this question

migrated from stackoverflow.com Mar 12 '12 at 15:19

This question came from our site for professional and enthusiast programmers.

2 Answers 2

try the following:

declare @timestamp datetime = getdate()
declare @cmd varchar(200)
set @cmd='bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-'+cast(@timestamp as varchar(50))+'" -T -c -t, '
EXEC xp_cmdshell @cmd
share|improve this answer

This gives date and time both and make it windows compatible filename as well

DECLARE @dt AS VARCHAR(30)
SELECT @dt = REPLACE( CONVERT(VARCHAR(26),getdate(),120),':','-')
EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-' + @dt +'" -T -c -t, '
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.