First Post: Set file name full date subtract 1 day

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
First_Try
Posts: 1
Joined: 08 Apr 2022 07:40

First Post: Set file name full date subtract 1 day

#1 Post by First_Try » 08 Apr 2022 08:09

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
Posts: 178
Joined: 06 Oct 2017 07:33

Re: First Post: Set file name full date subtract 1 day

#2 Post by atfon » 08 Apr 2022 09:23

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
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: First Post: Set file name full date subtract 1 day

#3 Post by Aacini » 08 Apr 2022 13:06

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

Antonio

miskox
Posts: 553
Joined: 28 Jun 2010 03:46

Re: First Post: Set file name full date subtract 1 day

#4 Post by miskox » 08 Apr 2022 13:55

First_Try wrote:
08 Apr 2022 08:09
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
Posts: 178
Joined: 06 Oct 2017 07:33

Re: First Post: Set file name full date subtract 1 day

#5 Post by atfon » 08 Apr 2022 14:03

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
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: First Post: Set file name full date subtract 1 day

#6 Post by Aacini » 08 Apr 2022 14:45

atfon wrote:
08 Apr 2022 14:03

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
Posts: 178
Joined: 06 Oct 2017 07:33

Re: First Post: Set file name full date subtract 1 day

#7 Post by atfon » 08 Apr 2022 20:32

Aacini wrote:
08 Apr 2022 14:45

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
Posts: 553
Joined: 28 Jun 2010 03:46

Re: First Post: Set file name full date subtract 1 day

#8 Post by miskox » 09 Apr 2022 04:56

What if SQLCMD somehow completes before midnight?

Saso

Post Reply