30

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
    Commented Nov 29, 2012 at 21:18
  • slhck, lol thanks i was wondering what was going on then I remembered I wasn't at stack overflow lol
    – guyfromfl
    Commented Nov 29, 2012 at 21:36
  • replacing %date% by %date:~6,4%-%date:~3,2%-%date:~0,2% worked
    – MagTun
    Commented Nov 27, 2015 at 10:05

7 Answers 7

38

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.

7
  • 1
    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
    Commented Nov 29, 2012 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
    Commented Nov 30, 2012 at 3:29
  • 1
    To get locale-independent date use wmic instead
    – phuclv
    Commented Aug 23, 2016 at 16:23
  • This solution can give you different result on different machines. Commented Feb 26, 2019 at 12:50
  • 2
    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
    Commented Aug 20, 2019 at 12:58
10

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
  • 4
    Welcome to Super User! Could you please edit your answer to include some explanation of what your commands do?
    – Excellll
    Commented Mar 5, 2015 at 22:20
  • 1
    You’re answering the wrong question.  The OP’s question asks for YYYYMMDD; this answer gives MMDDYYYY. Commented Jul 4, 2015 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
    Commented Oct 24, 2017 at 13:24
8

Sorry for the 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. Commented Jun 29, 2021 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
    Commented Jun 25, 2015 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. Commented Jul 4, 2015 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
0

The short answer in one line of code

set Timestamp=%DATE:~-4%%DATE:~4,2%%DATE:~7,2%%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%

If you want more details or customization take a look to the following code

echo Date format=%DATE%
set dd=%DATE:~7,2%
set mon=%DATE:~4,2%
set yyyy=%DATE:~-4%
echo.
echo Time format = %time%
set hh=%time:~0,2%
set min=%time:~3,2%
set ss=%time:~6,2%
set ms=%time:~9,2%
echo.

echo dd    %dd% 
echo mon    %mon% 
echo yyyy  %yyyy%
echo hh    %hh%
echo min    %min%
echo ss    %ss%
echo ms    %ms%


set Timestamp=%yyyy%%mon%%dd%%hh%%min%%ss%%ms%

echo Timestamp = %Timestamp%
1
  • Welcome to SuperUser! How is this better than the accepted answer? Commented Feb 3, 2023 at 7:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .