First Post: Set file name full date subtract 1 day

[First_Try]

Hi All, I currently have a batch running a SQLCMD and want to name the file output yyyy-mm-dd.csv but need to subtract a day from it. Seeing that the query finishes the following day.

I have searched around here and there is a lot of great information but could not find what I needed. My friend Google did give me some things to try and it worked for a couple days then today I received an error "Invalid number. Numeric constants are either decimal (17),
hexadecimal (0x11), or octal (021)."

Are there any examples available?

Any ideas would be a great help.

Thanks

[atfon]

It sounds like you are running into an issue where the script is generating a leading 0 that is specifying an Octal. See the section "Leading Zero will specify Octal" here:

https://ss64.com/nt/set.html

There are certainly workarounds:

viewtopic.php?t=4911#p28550

To determine the best way to adjust your code, it would help if you posted it and and used the code tag option.

[Aacini]

You may use this method to subtract 1 day to a date...

Antonio

[miskox]

Seeing that the query finishes the following day

Why don't you store your date before you run your SQL commands? In this way you don't have to subtract one day from the date when it completes.

Saso

[atfon]

Code: Select all

@echo off
setlocal enabledelayedexpansion
for /f "tokens=1-6 delims=/: " %%a in ('robocopy "|" . /njh ^| find ":"') do  (
    set "_year=%%a" & set "_month=%%b" & set "_day=%%c" 
    set /a "_day=1!_day!-100"
    set /a "_day=!_day! - 1"
    if !_day! LSS 10 set "_day=0!_day!"
    set "_date=!_year!-!_month!-!_day!"
)
echo %_date%

I was never great with modulus and there is probably an easier method, but this seems to give the right format. Someone with better knowledge of batch scripting can confirm.

[Aacini]

Code: Select all

@echo off
setlocal enabledelayedexpansion
for /f "tokens=1-6 delims=/: " %%a in ('robocopy "|" . /njh ^| find ":"') do  (
    set "_year=%%a" & set "_month=%%b" & set "_day=%%c" 
    set /a "_day=1!_day!-100"
    set /a "_day=!_day! - 1"
    if !_day! LSS 10 set "_day=0!_day!"
    set "_date=!_year!-!_month!-!_day!"
)
echo %_date%

I was never great with modulus and there is probably an easier method, but this seems to give the right format. Someone with better knowledge of batch scripting can confirm.

What happen if current day is the 1st one? One day before would be the last day of previous month... If the current day is January/1st, then one day before would be December/31 of previous year... If current day is March/1st the day before would be February/28, excepting if the year is a leap year...

Antonio

[atfon]

What happen if current day is the 1st one? One day before would be the last day of previous month... If the current day is January/1st, then one day before would be December/31 of previous year... If current day is March/1st the day before would be February/28, excepting if the year is a leap year...

Antonio

Good point. Antonio's is definitely the more robust & accurate solution.

[miskox]

What if SQLCMD somehow completes before midnight?

Saso