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
Using Powershell in Visual Studio Code this worked for me:
sqlcmd -S InstanceName,2026 -N -i JIRA_database_connections.sql -o $SS\JIRA\log\JIRA_database_connections_$otoday.txt -e
$SS and $otoday are Powershell environment variables I created in a startup script:
$SS=$ENV:SCRIPT_MSSQL
$OS=$ENV:SCRIPT_ORACLE
$otoday=Get-Date -Format "yyyyMMdd"
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"
setlocal 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%.csvPowerShell will let you control the formatting of the date. This removes any hardcoded dependence on a specific locale. If you are on a supported Windows system, PowerShell will be available.
I also replaced name with the standard USERNAME variable value.
FOR /F %A IN ('powershell -NoLogo -NoProfile -Command "Get-Date -Format 'yyyyMMdd-HHmm'"') DO (SET "DYNAMICDATE=%A")
sqlcmd -S INSTANCENAME -i "C:\Users\%USERNAME%\Desktop\test.sql" -o "C:\Users\%USERNAME%\Desktop\%USERNAME%_%DYNAMICDATE%.csv"
