Solved

Datetime in the filename

Posted on 2011-02-11
7
504 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:MPI_IT
7 Comments
 
LVL 2

Expert Comment

by:ScottJones74
ID: 34873372
ireport_2_11_11.xml
0
 
LVL 7

Expert Comment

by:tlovie
ID: 34873407
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'
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34873474
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 32

Expert Comment

by:ewangoya
ID: 34873504
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

0
 

Author Comment

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


0
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 34873931
try
declare @cmd varchar(128)
declare @filename varchar(128)

set @filename = 'IReport_' + CAST(DATEPART(YY, GETDATE()) as VARCHAR) + '_' + 
                             CAST(DATEPART(MM, GETDATE()) as VARCHAR) + '_' + 
                             CAST(DATEPART(DD, GETDATE()) as VARCHAR) + '_' +  
                             CAST(DATEPART(HH, GETDATE()) as VARCHAR) + '_' + 
                             CAST(DATEPART(MINUTE, GETDATE()) as VARCHAR) + '_' +
                             CAST(DATEPART(SS, 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

0
 

Author Closing Comment

by:MPI_IT
ID: 34874447
It worked perfectly. Thank you !
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question