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?

Solution

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="

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

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