Solved

Datetime in the filename

Posted on 2011-02-11
7
548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Ephraim Wangoya
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:
Ephraim Wangoya 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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