Add date to bcp file name

• NineIron

  SSChampion

  Points: 12529

  More actions

  April 7, 2016 at 4:24 am

  #325525

  I have an SQL Job set up to send a file to a folder. I need to add getdate() in the format, yyyymmdd, to the end of the filename but, I can't seem to get the correct syntax. Any thoughts?

  DECLARE @cmd VARCHAR(2048)

  SET @cmd = 'bcp "EXEC SigHealth..spAPSDownload_Lab" queryout'

  +' "\\sh-sftp\data$\aps_billing\APS_Lab.txt" -c -T -t^|'

  +' -SLOCALHOST" '

  print @cmd

  EXEC master..xp_cmdshell @cmd, NO_OUTPUT

• tripleAxe

  SSCertifiable

  Points: 5643

  More actions

  April 7, 2016 at 4:30 am

  #1870385

  DECLARE @DateStr VARCHAR(12);

  SET @DateStr = REPLACE(CONVERT(VARCHAR(12), GETDATE(),102),'.','');

  DECLARE @cmd VARCHAR(2048)

  SET @cmd = 'bcp "EXEC SigHealth..spAPSDownload_Lab" queryout'

  +' "\\sh-sftp\data$\aps_billing\APS_Lab_'+@DateStr+'.TXT" -c -T -t^|'

  +' -SLOCALHOST" '

  print @cmd

• NineIron

  SSChampion

  Points: 12529

  More actions

  April 7, 2016 at 5:10 am

  #1870391

  Perfect.

  Thanx.