Urgent please help--Append Date(yyyymmdd format) to filename (Example: tnvin_20161019.txt)

Question

Post reply

Urgent please help--Append Date(yyyymmdd format) to filename (Example: tnvin_20161019.txt)

patilpallavi16

Ten Centuries

Points: 1123
More actions
October 19, 2016 at 6:21 am

#331474

Hi Everyone,
I have this batch file as below. I need to update this to get the date without setting it as I have done in the second line of my code below.
I want it to display automatically when I run this batch file every time. The date should be in yyyymmdd format.
I tried lot of options with date parameters but couldn't get this right.
Can you please help me with this..
*** Batch file ********************************************
set naic=1211610
set filedatetime=18/10/2016 11:03
set date=%filedatetime:~6,4%%filedatetime:~3,2%%filedatetime:~0,2%
set filename=tnvin_%date%%naic%.txt
set tnpath=C:\tnvintrailer\
SQLCMD -S SQLSANDBOX -E -Q "[SouthernTrust].[dbo].[P_tntr] "
bcp [St].[dbo].[TmpTNVINTRInfo] out "%tnpath%%filename%" -c -T -S SQLSANDBOX
SQLCMD -S SQLSANDBOX -E -Q "DROP TABLE [St].[dbo].[TmpTNVINTRInfo]"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12314 More actions · Answer 1

SELECT REPLACE(REPLACE(REPLACE(CAST(CONVERT(VARCHAR(19), GETDATE(), 120) AS VARCHAR(10)),'-',''),':',''),' ','');

hth

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

andrew gothard (10/19/2016)
SELECT REPLACE(REPLACE(REPLACE(CAST(CONVERT(VARCHAR(19), GETDATE(), 120) AS VARCHAR(10)),'-',''),':',''),' ','');
hth

SELECT CONVERT(VARCHAR(10),GETDATE(),112)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

patilpallavi16 Ten Centuries Points: 1123 More actions · Answer 3

I think the code is for SQL. I need it to be done in batch script as shown in my question.

Thanks.

andrew gothard SSChampion Points: 12314 More actions · Answer 4

J Livingston SQL (10/19/2016)
andrew gothard (10/19/2016)
SELECT REPLACE(REPLACE(REPLACE(CAST(CONVERT(VARCHAR(19), GETDATE(), 120) AS VARCHAR(10)),'-',''),':',''),' ','');
hth
SELECT CONVERT(VARCHAR(10),GETDATE(),112)

Slightly neater I suppose 😉

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Add this to your stored procedure and keep a single call to the database instead of 3.

DECLARE @Bcp NVARCHAR(200);

SET @Bcp = N'bcp [St].[dbo].[TmpTNVINTRInfo] out "C:\tnvintrailer\tnvin_' + CONVERT(NCHAR(8), GETDATE(), 112 ) + N'1211610.txt" -c -T -S SQLSANDBOX';

EXEC xp_cmdshell @Bcp;

DROP TABLE [St].[dbo].[TmpTNVINTRInfo];

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

patilpallavi16 Ten Centuries Points: 1123 More actions · Answer 6

Thank You,

I will also try it out.

But I tried this .. and got the output.

**********************************************************************************

set naic=1261110

set tnpath=C:\tnvintrailerset year=%date:~-4,4%

echo year=%year%

set month=%date:~-10,2%

if "%month:~0,1%" == " " set month=0%month:~1,1%

echo month=%month%

set day=%date:~-7,2%

if "%day:~0,1%" == " " set day=0%day:~1,1%

echo day=%day%

set datetime=%year%%month%%day%

echo datetimef=%datetimef%

SQLCMD -S SQLSANDBOX -E -Q "[St].[dbo].[P_tntr] "

bcp [St].[dbo].[TmpTNVINTRInfo] out "%tnpath%tnvin_%datetime%%naic%.txt" -c -T -S SQLSANDBOX

SQLCMD -S SQLSANDBOX -E -Q "DROP TABLE [St].[dbo].[TmpTNVINTRInfo]"

****************************************************************************************