Search code examples
peoplesoftsqr

Subtracting 1 day from date variable causing errors

I have an SQR program that uses a field called $EFFDT. It is NOT explicitly declared in the program as a date variable. What it currently does is get initially set to '' (blank) as follows:

LET $EFFDT = ' '

$EFFDT then gets set by reading a file from a field called $INPUT

read 1 into $Input:80 LET $EFFDT = substr($INPUT,53,8)

It then re-formats the date to get it into the format YYYY-MM-DD

do Format-DateTime($EFFDT,$EFFDT,{Prompt-Mask},'','native')

What I want to do in a later procedure is set $EFFDT to the $EFFDT value minus 1 day

What I tried adding in a later procedure is this:

LET $EFFDT = DATEADD($EFFDT, 'day', -1)

When I try running the SQR I get the SQR 4045 Error (Function or operator 'dateadd' requires a date argument'

From what I read this means that the $EFFDT variable needs to be declared as a date variable. So I explicitly declared this variable as a date, however then it errors when the program runs this part of the code:

LET $EFFDT = ' '

SQR 1944 - The date ' ' is not in the format specified by SQR_DB_DATE_FORMAT or in one of the accepted formats listed below....

So I'm not sure exactly how to proceed to either get around the original error or the 2nd error above. I ultimately need to use the $EFFDT - 1(day) as a field in a SQL Insert command. Any help is appreciated!

Solution

  • Try this

    LET $EFFDT = ' '
! ...
LET $EFFDT = substr($INPUT,53,8)  
! At this point, I assume $EFFDT looks something like 20200228.  If not, change the mask below
LET $RESULT = DATEADD(STRTODATE($EFFDT, 'YYYYMMDD'), 'day', -1)

    It seems that you have to do the STRTODATE on the same line as the DATEADD. For instance, the code below doesn't work for me

    LET $EFFDT = ' '
! ...
LET $EFFDT = substr($INPUT,53,8)  
! At this point, I assume $EFFDT looks something like 20200228.  If not, change the mask below
LET $TEMPDT = STRTODATE($EFFDT, 'YYYYMMDD')
LET $RESULT = DATEADD($TEMPDT, 'day', -1)