asked on

Datetime in the filename

I have the following job running daily and dropping a file to the specified location.

EXEC xp_cmdshell 'bcp "exec ErX.dbo.sp_Ap1" queryout \\vat\users\L\IReport.xml -c -T'

How do I incorporate datetime into the file name IReport.
Thanks!

Scott

ireport_2_11_11.xml

tlovie

This might work:

EXEC xp_cmdshell 'bcp "exec ErX.dbo.sp_Ap1" queryout \\vat\users\L\IReport' + convert(varchar(8), getdate(), 112) + '.xml -c -T'

Ephraim Wangoya

declare @cmd varchar(128)
declare @filename varchar(24)

set @filename = 'IReport_' + CAST(DATEPART(Y, GETDATE()) as VARCHAR) + '_' +
CAST(DATEPART(M, GETDATE()) as VARCHAR) + '_' +
CAST(DATEPART(D, GETDATE()) as VARCHAR) + '.xml'
set @cmd = 'bcp "exec ErX.dbo.sp_Ap1" queryout \\vat\users\L\' + @filename + ' -c -T'
EXEC xp_cmdshell @cmd

Ephraim Wangoya

Change it to

declare @cmd varchar(128)
declare @filename varchar(24)

set @filename = 'IReport_' + CAST(DATEPART(YY, GETDATE()) as VARCHAR) + '_' + 
                             CAST(DATEPART(MM, GETDATE()) as VARCHAR) + '_' + 
                             CAST(DATEPART(DD, GETDATE()) as VARCHAR) + '.xml'
set @cmd = 'bcp "exec ErX.dbo.sp_Ap1" queryout \\vat\users\L\' + @filename + ' -c -T'                             
EXEC xp_cmdshell @cmd

Open in new window

MPI_IT

ASKER

Hi ewangoya,
It woked well with date part. Is it possible to incorporate time in the file name too (hhmmss) ?
Thanks !

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MPI_IT

ASKER

It worked perfectly. Thank you !