Sqlcmd Subtract date

Question

I have created the following batch file to run a query, and save the results in a .csv file.

sqlcmd -S MyLogin -i LocationToSql -E -o "C:\Users\user\Desktop\query result\result2-%date:~-4,4%-%date:~-7,2%-%date:~-10,2%.csv" -s";" -w 700

Right now, i'm saving the result as: result2-2017-06-21.csv
However, i would like it to be: result2-2017-06-20.csv

But i don't know how to subtract 1 day of the command.
The date is defined like:

%date:~-4,4%-%date:~-7,2%-%date:~-10,2%

[This post may be helpful](https://stackoverflow.com/questions/355425/date-arithmetic-in-cmd-scripting) — G42, Jun 21 '17 at 12:37
Most probably because of lack of own research/efforts -- see this: [ask] — aschipfl, Jun 21 '17 at 16:22
You could borrow date arithmetic functions from another language, like PowerShell, VBScript, JavaScript, which are all native to modern Windows systems; the Windows command prompt and batch files are not capable of such... — aschipfl, Jun 21 '17 at 16:26

score 1 · Accepted Answer · answered Jun 21 '17 at 12:40

I slightly modified a script I recently posted here:

setlocal enabledelayedexpansion

set day=%date:~-10,2%
set month=%date:~-7,2%
set year=%date:~-4,4%

echo.%day%|findstr /r /b "0">nul&&set day=%day:~1%
echo.%month%|findstr /r /b "0">nul&&set month=%month:~1%
set /a day-=1
if %day% lss 1 (
    set /a day+=30
    set /a month-=1
    if !month!==2 (set /a day-=2
                   set /a leap=year%%4
                   if !leap!==0 set day+=1
    )
    for /l %%# in (1 2 7) do if %month%==%%# set /a day+=1
    for %%# in (8 10 12) do if %month%==%%# set /a day+=1
    if !month!==0 (
        set month=12
        set /a year-=1
    )
)
set day=0%day%
set month=0%month%

echo %year%-%month:~-2%-%day:~-2%

This will echo the date of yesterday in YYYY-MM-DD format. You can also edit the line with set /a day-=1 to subtract mutliple days.

Sqlcmd Subtract date

1 Answers1