correct format for datetime appended to filename

Question

I'm trying to setup a batch file to execute a set of stored procs and dump the output to a timestamped text file. I'm having problems finding the correct format for the timestamp.

Here is what I'm using

osql.exe -S <server> -E -Q "EXEC <stored procedure> " -o "c:\filename_%date:~-0,10%_%time:~-0,10%.txt"

The error I get is:

Cannot open output file - x:\filename_Thu 06/25/_16:26:43.1.txt No such file or directory

I can't find the documentation and I've played around with it but can't find the correct format.

Answer 1

: is not a valid character in a filename on windows (Along with " \ / * ? < > and |), I'm guessing the expansion of %time contains the :'s separating hours, minutes and seconds.

A stab in the dark could be replacing %time:~0,10% with something like:

%time:~0,2%_%time:~3,2%_%time:~6,2%

This assumes that %time% always will be on the form "hh:mm:ss.ss", and I think this depends on your locale (I.e. my locale uses a , instead of a . to separate seconds from 1/100 seconds). It should give you something like hh_mm_ss instead of hh:mm:ss. If you want the first digit of 1/100 seconds (as you have in your original example) you could change the last variable expansion to take character-range 6,4 instead of 6,2.

So, at frist glance I overlooked the /'s which also is invalid in a filename, you probably also want to remove those as well. Assuming that %date% will be on the format "ddd DD/MM/yyyy" you could try something like (my locale differs from this so untested):

%date:~4,2%_%date:~7,2%_%date:~10,4%

For testing all this, if you're not already doing it, I suggest opening cmd.exe and just typing it in there. "echo %time:~0,2%etc.etc." to find something that fits with your locale.

Finally, a word of warning, this basically assumes a specific locale, it's unlikely to be portable to different locales.

Answer 2

I would advise you to use a more conventional date format, which also lends itself to easy sorting - yyyymmddhhmmss. i.e. Don't use sepperators at all. When you first look at such a format it may look hard to read but you get over that in about 2.5 seconds.

Answer 3

If you want universal format for date try this.

for /F "skip=1 tokens=2-4 delims=(-)" %%A in ('echo ^| date') do for /F "Tokens=2-4 Delims=/ " %%X in ('date /t') do set %%A=%%X & set %%B=%%Y & set %%C=%%Z

echo today is %dd%%mm%%yy%

This is very useful for servers following different date formats like mm/dd/yyyy or dd/mm/yyyy This will always make sure that you get month in variable mm, day in dd and year in yy