DOS Command Script: Use Date/Time in File Names

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

Join the Conversation

6 Comments

  1. 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

  2. Thanks a lot Bill…. I was badly searching for suppressing the leadin space in the ‘hours’ output.

  3. 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.

  4. 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.

  5. Very helpful and useful set of commands/script.

    Definite mandatory add to my toolbox folder.

Leave a comment

Your email address will not be published. Required fields are marked *