How to get the date format in windows batch file as 'yyyymmdd'?

Question

I am writing a Windows Batch file and the batch file is supposed to output a file in a specific folder. I want the filename to be written as follows:

filename-yyyymmdd.csv

where yyyymmdd stands for the current date.

My batch file codes stand as follows:

cd:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn
bcp "SELECT TOP 100 * FROM xxxx.dbo.xxxxx" queryout c:\test\csv\confirmed.csv -t, -c -S xxxx\xxxxx -U xxx -P xxxxxxxxxx
set mydate=%date:~10,4%%date:~4,2%%date:~7,2%
echo %mydate%
copy c:\test\csv\confirmed.csv c:\test\csvwithdates\confirmed-%mydate%.csv

I am getting the following filename as output: confirmed-ay18.csv

Desired output: confirmed-20180528.csv

I had a look into the following questions on StackOverflow but I am having a hard time implementing the suggested answers:

Get Date in YYYYMMDD format in windows batch file

Windows batch: formatted date into variable

What am I doing wrong?

Answer 1

The dynamic environment variable DATE supplies the current local date in region dependent format, i.e. according to the country set in Windows Region and Language options for used account. Wikipedia article Date format by country lists the various date formats used in countries all over the world.

So it is necessary to run with the account used on running the batch file in a command prompt window echo %DATE% and look on output date string.

Does output date start with an abbreviated weekday or is the date output without weekday? Is first output day and next month with digits or is first output month and next day? Is the month output as number or with name? Is day of month or the month with a value less than 10 output with a leading 0 and so always with two digits or with just one digit?

A very common command line for modifying a region dependent date string in the format ddd, dd.MM.yyyy with . or / or any other delimiter character to yyyyMMdd is:

set "LocalDate=%DATE:~-4%%DATE:~-7,2%%DATE:~-10,2%"

The date string is substituted from right to make string substitution independent on presence of weekday at beginning of date string.

For region dependent date string ddd, MM/dd/yyyy the command line to use is:

set "LocalDate=%DATE:~-4%%DATE:~-10,2%%DATE:~-7,2%"

Which string substitution is necessary can be easily find out by running the commands in a command prompt window with using command ECHO instead of SET:

echo %DATE%
echo %DATE:~-4%%DATE:~-7,2%%DATE:~-10,2%
echo %DATE:~-4%%DATE:~-10,2%%DATE:~-7,2%

See also What does %date:~-4,4%%date:~-10,2%%date:~-7,2%_%time:~0,2%%time:~3,2% mean?

The advantage of using dynamic environment variable DATE is the speed as this string substitution is done by Windows command processor very fast. The disadvantage is the varying date format because of region/country dependency.

A region independent solution is using command WMIC as demonstrated below:

for /F "tokens=2 delims==." %%I in ('%SystemRoot%\System32\wbem\wmic.exe OS GET LocalDateTime /VALUE') do set "LocalDate=%%I"
set "LocalDate=%LocalDate:~0,8%"

WMIC outputs the date string always in the format yyyyMMddHHmmss.microsecond±UTC offset in minutes independent on which country is configured and which date format is set for the country.

The disadvantage of using WMIC is the time required for execution as it needs more than a second to output the date string UTF-16 Little Endian encoded processed next by command FOR.

See answer on Why does %date% produce a different result in batch file executed as scheduled task? for a very detailed explanation on how those two command lines work on execution by Windows command processor cmd.exe.

Complete batch file:

@echo off
cd /D "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
bcp.exe "SELECT TOP 100 * FROM xxxx.dbo.xxxxx" queryout C:\test\csv\confirmed.csv -t, -c -S xxxx\xxxxx -U xxx -P xxxxxxxxxx
for /F "tokens=2 delims==." %%I in ('%SystemRoot%\System32\wbem\wmic.exe OS GET LocalDateTime /VALUE') do set "LocalDate=%%I"
set "LocalDate=%LocalDate:~0,8%"
copy /Y C:\test\csv\confirmed.csv C:\test\csvwithdates\confirmed-%LocalDate%.csv
set "LocalDate="

Another disadvantage of the WMIC solution is that it could not be installed anymore by default on latest Windows 10/11 because of Microsoft declared this executable as deprecated and it became an optional Windows component which is not installed anymore by default on latest Windows 10/11 on not being installed with an upgrade of a former version of Windows which had installed wmic.exe by default.

Another region independent solution is using the error output of ROBOCOPY by using:

set "LocalDate=" & for /F "tokens=1-3 delims=/ " %%G in ('%SystemRoot%\System32\robocopy.exe "%SystemDrive%\|" . /NJH') do if not defined LocalDate set "LocalDate=%%G%%H%%I"

This command line is explained in full details in the answer Time is set incorrectly after midnight. It requires %SystemRoot%\System32\robocopy.exe which exists by default in the Windows System32 directory since Windows Vista and Windows Server 2003. robocopy.exe of Windows Server 2003 can be copied into the Windows System32 directory of Windows XP.

The complete batch file with this solution is:

@echo off
cd /D "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
bcp.exe "SELECT TOP 100 * FROM xxxx.dbo.xxxxx" queryout C:\test\csv\confirmed.csv -t, -c -S xxxx\xxxxx -U xxx -P xxxxxxxxxx
set "LocalDate=" & for /F "tokens=1-3 delims=/ " %%G in ('%SystemRoot%\System32\robocopy.exe "%SystemDrive%\|" . /NJH') do if not defined LocalDate set "LocalDate=%%G%%H%%I"
copy /Y C:\test\csv\confirmed.csv C:\test\csvwithdates\confirmed-%LocalDate%.csv
set "LocalDate="

To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.

• cd /?
• copy /?
• echo /?
• for /?
• robocopy /?
• set /?
• wmic /?
• wmic os /?
• wmic os get /?
• wmic os get localdatetime /?