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
I didn’t know about the hidden date en time variables. Didn’t know about the substring stuff too.
Always nice to learn something new about the old techniques! tnx
Thanks a lot Bill…. I was badly searching for suppressing the leadin space in the ‘hours’ output.
Hi, I just want the system date appended at the end of the output file name, e.g. report-20090707.txt. Do you have a simpler command? Thanks.
skpsaid, just use part of it then (report-%date:~-4,4%%date:~-10,2%%date:~-7,2%)
Thanks…programming in dos always makes me yearn for a nice monochrome greenscreen…and for some odd reason early 80s funk.
I suggest hunting for the game Asylum, and giving it a play, its the first ascii first person that I can recall.
report-%date:~-4,4%%date:~-10,2%%date:~-7,2%
That format is very helpful, thank you.
Very helpful and useful set of commands/script.
Definite mandatory add to my toolbox folder.