Solved

Datetime in the filename

Posted on 2011-02-11
7
476 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now