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
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"