Adding Timestamp to Exported Filename in SQL Server Management Studio

Question

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?

Answer 1

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, '

Answer 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