Page 1 of 1
First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 08:09
by 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
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 09:23
by 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.
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 13:06
by Aacini
You may use
this method to subtract 1 day to a date...
Antonio
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 13:55
by miskox
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
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 14:03
by 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.
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 14:45
by Aacini
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
Re: First Post: Set file name full date subtract 1 day
Posted: 08 Apr 2022 20:32
by atfon
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.
Re: First Post: Set file name full date subtract 1 day
Posted: 09 Apr 2022 04:56
by miskox
What if SQLCMD somehow completes before midnight?
Saso