Putting Timestamp on the output file using sqlcmd in batch file

Question

I have written the foll sql query in a batch file to fetch the result from a remote server.

SQLCMD -S "ServerName -U "uer" -P "Pass" -W -d"Interface" -Q"select LEFT(Zone_Prefix,5)+case when RIGHT(Zone_prefix,1)='N' then '_NORTH_WING' ELSE '_SOUTH_WING' END AS [Zone Name],cnt as [Zone Count] from ( SELECT  LEFT([Zone Name], 6) AS Zone_Prefix,SUM([Zone Count]) cnt FROM [Interface].[dbo].[VwZoneCount] WHERE [Zone Name] IN ('EB2GFNMZ','EB2GFSMZ','EB2GFNZ1','EB2GFSZ1','EB21FNZ1','EB21FSMZ','EB21FSZ1','EB22FNZ1','EB22FSZ1','EB22FSMZ','EB23FNMZ','EB23FNZ1','EB23FNZ2','EB23FNZ3','EB23FSMZ','EB23FSZ1','EB23FSZ2','EB24FNMZ','EB24FNZ1','EB24FSMZ','EB24FSZ1','EB25FNMZ','EB25FNZ1','EB25FSMZ','EB25FSZ1','EB26FNMZ','EB26FNZ1','EB26FSMZ','EB26FSZ1','EB27FNZ1','EB27FSMZ') GROUP BY LEFT([Zone Name], 6))tbl" -s"    " -o"Output_hvac.xls"

I am executing this query at every 4 hours using task scheduler . At present for every execution the result is getting overwritten . I need after every execution the new result gets appended or store in different file with a Time Stamp . My main concern is putting a timestamp and keeping all the previous executed data.

Thanks

dbenham · Accepted Answer · 2013-02-26 14:00:22Z

1

Adding a time stamp to a file name is certainly possible, but it is a pain in batch.

Your first option of appending is much simpler. Eliminate your -o option, and use append redirection instead. Adding the time stamp just requires a redirected ECHO statement.

>>"Output_hvac.xls" echo %date% %time%
>>"Output_hvac.xls" SQLCMD ...your_options_minus_the_-o_option...

or

>>"Output_hvac.xls" (
  echo %date% %time%
  SQLCMD ...your_options_minus_the_-o_option...
)

answered Feb 26, 2013 at 14:00

dbenham

132k29 gold badges272 silver badges410 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anish Kumar · Accepted Answer · 2018-10-16 08:31:22Z

0

Try:

-o "Output_hvac_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.xls"

Output should look like this:

Output_hvac_20181016_ 919.xls

answered Oct 16, 2018 at 8:31

Anish Kumar

1

Collectives™ on Stack Overflow

Putting Timestamp on the output file using sqlcmd in batch file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related