Q:

How to dynamically name output of a sql file in sqlcmd

Objective:
I would like to create a CSV dump on a weekly basis. I would like to name my file dynamically with the date.
The current code I have is:
sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name_$DYNAMIC$DATE.csv

A:

Give this a try:
set timehour=%time:~0,2%
sqlcmd -S INSTANCENAME -i c:\Users\name\Desktop\test.sql -o c:\Users\name\Desktop\name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

You can try seeing the output by using Echo
set timehour=%time:~0,2%
echo name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

%date% returns current date in short format. The ":~6,4" part is like a
  SUBSTRING function which returns 4 characters starting from position
  6, which returns Year. Similarly, retrieving month, day, hour,
  minutes using same function and appending all of this together to
  generate the file name in format "name-YYYYMMDD-HHMM"

