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

Comments (8)

  1. ArnoB says:

    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. Jitendra says:

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

  3. skpsaid says:

    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. bill says:

    skpsaid, just use part of it then (report-%date:~-4,4%%date:~-10,2%%date:~-7,2%)

  5. Evan B says:

    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.


    That format is very helpful, thank you.

  6. DLKirchmer says:

    Very helpful and useful set of commands/script.

    Definite mandatory add to my toolbox folder.

  7. DutchGlory says:

    when i run:

    @echo off


    SET  DATETIME=%date:~0,2%_%date:~-7,2%_%date:~-4,4%_%time:~0,2%

    ECHO "TEST…" > test1-%date:~0,2%_%date:~-7,2%_%date:~-4,4%_%time:~0,2%.txt

    ECHO "TEST…" > test2-%DATETIME%.txt

    i get the same result.  "test1-do_06_2015_21.txt" and  "test2-do_06_2015_21.txt"

    "day_month_year_hour"  (no minutes or seconds)