Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
I understand, but what format would remove the colon? –  jhayes Jun 25 '09 at 22:43
    
I think you'd either have to parse out the digits you want to discard the colons, or use hour/minute/second variables, if they exist. –  Kara Marfia Jun 25 '09 at 23:08
    
still giving 'system cannot find the file specified' but this is on the right track, I'll keep tinkering until I make it work. Thanks. –  jhayes Jun 25 '09 at 23:23
    
Overlooked the / at first since well, we all know that / is the proper directory separator ;) –  Kjetil Joergensen Jun 25 '09 at 23:26
    
got it to work for me, after being stupid for a bit, as "c:\filename_%date:~4,2%_%date:~7,2%_%date:~10,4%_%time:~0,2%_%time:~3,2%_%time:‌​~6,2%.txt" which gives: filename_06_26_2009_10_59_48.txt Thanks for helping the clueless :) –  jhayes Jun 26 '09 at 16:02
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.