26

I am running a program from a batch file, which when it is done performs an automatic backup of my MySQL database.

I would like the batch file to create a different back up for each run, so I can backtrace.

The desired filename would be gnucash_shockwave-20121128210344.sql (Date format YYYY-MM-DD-HH-MM-SS)

I have googled a few things that said try %DATE:~4% and %Date.Year% but I get an error that says The system cannot find the specified path.

If I remove the attempt to timestamp it, the script works fine, but over writes the previous backup

Here is the section of code I'm talking about:

@REM *** EXECUTION ***
echo. Starting backup...
SET timestamp %DATE:~-4%%DATE:~4,2%%DATE:~7,2%%TIME%
%mysqldir%\mysqldump -u %mysqluser% -p%mysqlpassword% -h %mysqlhost% -P %mysqlport% --databases --routines --verbose gnucash_shockwave > %BackupDir%\gnucash_shockwave-%timestamp%.sql

echo.------------------------------------------------------
echo. Backup complete!

Any suggestions?

3
  • I've reassociated your accounts, you should be able to log in and comment on your own posts and answers to your questions now.
    – slhck
    Nov 29 '12 at 21:18
  • slhck, lol thanks i was wondering what was going on then I remembered I wasn't at stack overflow lol
    – guyfromfl
    Nov 29 '12 at 21:36
  • replacing %date% by %date:~6,4%-%date:~3,2%-%date:~0,2% worked
    – MagTun
    Nov 27 '15 at 10:05
35

The Date and Time format depends on what you've specified in the Region and Language Control Panel applet.

Run the following batch file (which assumes dd/mm/yyyy and hh:mm:ss) and modify the substring extraction (using the : and ~ characters) as required to get the proper parts from both Date and Time strings:

@echo off
cls
echo Date format = %date%
echo dd = %date:~0,2%
echo mm = %date:~3,2%
echo yyyy = %date:~6,4%
echo.
echo Time format = %time%
echo hh = %time:~0,2%
echo mm = %time:~3,2%
echo ss = %time:~6,2%
echo.
echo Timestamp = %date:~6,4%-%date:~3,2%-%date:~0,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%

For more help on substring extraction, type set /? at the command prompt or see this page.

6
  • Thanks, I think I understand how this is working now. I am not at the office, but will try that as soon as I get there and post back. We are using Windows 8 Thanks for the help
    – guyfromfl
    Nov 29 '12 at 20:51
  • Got it, Had to tweak the string locations of the date, probably because of my location and language but that did the trick! Thank you very much!
    – guyfromfl
    Nov 30 '12 at 3:29
  • 1
    To get locale-independent date use wmic instead
    – phuclv
    Aug 23 '16 at 16:23
  • This solution can give you different result on different machines. Feb 26 '19 at 12:50
  • 1
    I think something changed with the date output, I get > echo yyyy = %DATE:~6,4% to produce yyyy = /20/. However echo yyyy = %DATE:~10,4% is yyyy = 2019.
    – teeks99
    Aug 20 '19 at 12:58
9

Here's what worked for me.

Format - mmddyyyy_HHMMSS

echo %DATE% %TIME%

Date format = Thu 03/05/2015 15:48:26.22

echo mm = %date:~4,2%

echo dd = %date:~7,2%

echo yyyy = %date:~10,4%

echo Timestamp = %date:~4,2%%date:~7,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%

Timestamp - 03052015_154013
3
  • 3
    Welcome to Super User! Could you please edit your answer to include some explanation of what your commands do?
    – Excellll
    Mar 5 '15 at 22:20
  • 1
    You’re answering the wrong question.  The OP’s question asks for YYYYMMDD; this answer gives MMDDYYYY.
    – Scott
    Jul 4 '15 at 8:53
  • nothing like coming to Stack with a question and having the perfect answer just sitting there waiting to save the day for me. Thanks so much @abbs and Excelll
    – clockwiseq
    Oct 24 '17 at 13:24
5

Sorry for dthe delay...

The only reliable way to get appropriate date whatever regional setting are is the solution of foxidrive @ https://stackoverflow.com/questions/11037831/filename-timestamp-in-windows-cmd-batch-script

@echo off
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"

set "datestamp=%YYYY%%MM%%DD%" & set "timestamp=%HH%%Min%%Sec%"
set "fullstamp=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%"
echo datestamp: "%datestamp%"
echo timestamp: "%timestamp%"
echo fullstamp: "%fullstamp%"
pause
1
  • Appreciate this. I tried a bunch of the others and this one worked best for me. I could insert %datestamp% right into a filename. Jun 29 at 18:18
3

With a little tweaking I got this

@echo off
cls
echo Date format = %date%
echo dd = %date:~0,2%
echo mm = %date:~3,2%
echo yyyy = %date:~6,8%
echo.
echo Time format = %time%
echo hh = %time:~0,2%
echo mm = %time:~3,2%
echo ss = %time:~6,2%
echo.
echo Timestamp = %date:~0,2%_%date:~3,2%_%date:~6,8%-%time:~0,2%_%time:~3,2%_%time:~6,2%
ECHO "TEST..." > test-%date:~0,2%_%date:~3,2%_%date:~6,8%-%time:~0,2%_%time:~3,2%_%time:~6,2%.txt


Result:

"test-do_25_06-2015-22_21_51.txt" (Thursday_25th_June_2015-22:21.51)

2
  • How does this differ from the other answers?
    – suspectus
    Jun 25 '15 at 20:48
  • @suspectus: This answer differs from abbs’s answer in that abbs’s assumes a U.S. Region (i.e., locale), where %DATE% ~ Ddd MM/DD/YYYY, whereas this one assumes a non-U.S. Region, where %DATE% ~ DD/MM/YYYY (without the day of the week).  And this one differs from Karan’s answer in that this one is wrong — the question asks for YYYYMMDD; this answer gives DD_MM–YYYY.
    – Scott
    Jul 4 '15 at 8:54
1

i think this improves @Nick Rogers answer a little bit.

EX: with the german locate (de_DE) there is a leading whitespace at hours below 10.

also i wanted a leading zero at the hour. "echo - !^_hh! -" gives "09" instead of only "9"

so i break the code in two pieces for better reading. this example should match more locales.

+bonus: _ms = milliseconds or whatever the last two digits are (-;

remark: sometimes the HOUR needs to be escaped, see inside code

SETLOCAL
SETLOCAL EnableExtensions
SETLOCAL EnableDelayedExpansion
@ECHO ON
@rem use FOR /F to 'break out' the components of %DATE% and %TIME%, assuming 'yyyy/mm/dd' format date i.e.
@rem works also with german standard format dd.mm.yyyy

echo ----- date -----
    echo _%date%_
    for /F "tokens=1-3 delims=.-/" %%i IN ("%date%") DO Set "_Y=%%i"& Set "_M=%%j"& Set "_D=%%k"
    @rem Switch the year and day if appropriate
    IF "xx%_D:~2%" == "xx" Set "_Y=%_D%"& Set "_D=%_Y%"
    set __date=%_Y%%_M%%_D%
    echo _%__date%_
echo ----- date -----

echo ----- time -----
    echo _%time%_
    for /F "tokens=1-4 delims=:," %%l IN ("%time%") DO Set "_tmp_hh=%%l"& Set "_mm=%%m"& Set "_ss=%%n"& Set "_ms=%%o"
    @rem add leading zero to hour if necessary
    if %_tmp_hh% LSS 10 (set _hh=^0%_tmp_hh:~1,1%) ELSE (set _hh=%time:~0,2%)
    @rem sometimes the HOUR needs to be escaped, like this
    echo - !^_hh! -
    set __time=%_hh%%_mm%%_ss%%_ms%
    echo _%__time%_
echo ----- time -----

    Set fullTime=%_Y%%_M%%_D%-%_hh%%_mm%%_ss%%_ms%
    echo _%fullTime%_
pause

outputs: echo fullTime = 15062018-10182821

0

the shortest (most versatile?) answer is as per the following TimeStamp.cmd script containing just three lines of code (excluding comments etc.)...

@ECHO ON
@REM use FOR /F to 'break out' the components of %DATE% and %TIME%, assuming 'yyyy/mm/dd' format date i.e.

    for /F "tokens=1-6* delims=.:-/ " %%i IN ("%DATE% %TIME%") DO Set "YYYY=%%i"& Set "MM=%%j"& Set "DD=%%k"& Set "HH=%%l"& Set "MI=%%m"& Set "SS=%%n"

@REM Switch the year and day if appropriate

    IF NOT "X%DD:~2%" == "X" Set "YYYY=%DD%"& Set "DD=%YYYY%"

    Set "TimeStamp=%YYYY%%MM%%DD%%HH%%MI%%SS%"

Note the need to use %% rather than % for the variable in a FOR statement in a batch file

...execution yields (blank lines and comments removed)...

C:\>timestamp.cmd
C:\>for /F "tokens=1-6* delims=.:-/ " %i IN ("2018/03/02 11:17:43.35") DO Set "YYYY=%i"  & Set "MM=%j"  & Set "DD=%k"  & Set "HH=%l"  & Set "MI=%m"  & Set "SS=%n"
C:\>Set "YYYY=2018"  & Set "MM=03"  & Set "DD=02"  & Set "HH=11"  & Set "MI=17"  & Set "SS=43"
C:\>IF NOT "X" == "X" Set "YYYY=02"  & Set "DD=2018"
C:\>Set "TimeStamp=20180302111743"
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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