Dynamic file creation with BCP Utility

Question

I'm using BCP Utility to copy records out of table before deleting the records. The function is working just fine, however, I need to copy the records to a new file for the every time I delete, instead of override the same file (as it is now). It could be creating a new file with timestamp as prefix or something similar. Any ideas? My code

Declare @cmd varchar(1000) = 'bcp "select * from ##DeletedRecords" queryout 
"C:\Delete\DeletedRecord.txt" -t, -c -T'
print @cmd
EXEC master..XP_CMDSHELL @cmd  

Answer 1

just change the filename in the BCP command accordingly by appending date & time to the filename

example :

Declare @cmd varchar(1000);

select @cmd = 'bcp "select * from ##DeletedRecords" queryout '
            + '"C:\Delete\DeletedRecord' 
            + convert(varchar(10), getdate(), 112)    -- YYYYMMDD
            + replace(convert(varchar(10), getdate(), 108), ':', '') -- HHMMSS
            + '.txt" -t, -c -T'

print @cmd

EXEC master..XP_CMDSHELL @cmd