Nowadays, we have different choices of script languages in Windows: VBScript, JScript, PowerShell script, etc. Coming from the old DOS world, I still prefer a simple batch (.cmd) script.
Say we have a simple sql query and we want to run it every 10 minutes and save the output to a file. We can simply have a scheduled job to run a .cmd file with the following content:
sqlcmd -i query.sql -o report.txt
If we want to keep a history of the result, it is best if we can have the date and time in the file name. So let us change it to
sqlcmd -i query.sql -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%time:~0,2%%time:~3,2%.txt
The above command will run perfect between 10am and 11:59pm. Between 12am and 9:59am, however, %time:~0,2% will have a space (such as ” 9″) since %time% formats hours without leading 0.
“time /t” command will always show hours with leading 0. So we have another idea:
for /f “tokens=1,2 delims=: ” %%i in (‘time /t’) do sqlcmd -i query.sql -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%%i%%j.txt
The only problem here is “time /t” is in 12hr format. So you could overwrite earlier results. It seems we still should use %time% and it will be perfect if we replace the space with 0 in the hours. So here is the final script which does the job:
@echo off
setlocal
set timehour=%time:~0,2%
sqlcmd -i query.sql -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.txt