Is there a way to get the day of the week from a date in SQR?

Question

I am trying to write a program that will find the Saturday and Sunday of Each week when given a start date, and will end when it reached the end date. I am automating an old SQR program so we don't have to go in there and manually put in the dates for each Saturday and Sunday within the date range. I know SQL has a weekday() function, and I was wondering if there was a way to achieve a similar result in SQR. And I know that currently my code is FILLED with syntax errors because I have been trying to fix one piece at a time. But any advice/help works.

LET $start_day = WEEKDAY(&AC.FROM_DATE)
   LET $end_day = WEEKDAY(&AC.TO_DATE)
   !LET $start_day = &AC.FROM_DATE
   !LET $end_day = &AC.TO_DATE
   !LET $start_day = &AC.FROM_DATE(1, 'YYYY-MM-DD')
   !LET $end_day = &AC.TO_DATE(1, 'YYYY-MM-DD')


  LET $sat1 = DATEADD(&AC.FROM_DATE, 'day' ,5 - $start_day)
  LET $sun1 = DATEADD(&AC.FROM_DATE, 'day' ,6 - $start_day)

 !I KNOW THAT MY DATEADD FUNCTION IS NOT CORRECT...I THINK
  IF $sun1 < $end_day
    LET $sat2 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 7, 'day')
    LET $sun2 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 7, 'day')

    IF $sun2 < $end_day
      LET $sat3 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 14, 'day')
      LET $sun3 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 14, 'day')

      IF $sun3 < $end_day
        LET $sat4 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 21, 'day')
        LET $sun4 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 21, 'day')

        IF $sun4 < $end_day
          LET $sat5 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 28, 'day')
          LET $sun5 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 28, 'day')

          IF $sun5 < $end_day
            LET $sat6 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 35, 'day')
            LET $sun6 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 35, 'day')

            IF $sun6 < $end_day
              LET $sat7 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 42, 'day')
              LET $sun7 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 42, 'day')

              IF $sun7 < $end_day
                LET $sat8 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 49, 'day')
                LET $sun8 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 49, 'day')

                IF $sun8 < $end_day
                  LET $sat9 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 56, 'day')
                  LET $sun9 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 56, 'day')
                END-IF
              END-IF
            END-IF
          END-IF
        END-IF
      END-IF
    END-IF
  END-IF

Answer 1

I don't have SQR on this computer, but if I remember correctly, you can do something like this.

create-array name=weekend size=100 extent=10
    field=sun:date
    field=sat:date

let #weekend = 0
let $FD = dateToStr(&AC.FROM_DATE, 'YYYYMMDD')
let $TD = dateToStr(&AC.TO_DATE, 'YYYYMMDD')
while $FD <= $TD
    let $FD = strToDate($FD, 'YYYYMMDD')
    evaluate dateToStr($FD, 'D')
        when = '1'
             let weekend.sun(#weekend) = $FD
             let $FD = dateToStr(dateAdd($FD, 'DAY', 6), 'YYYYMMDD')
             break
        when = '2'
             let weekend.sat(#weekend) = $FD
             let $FD = dateToStr(dateAdd($FD, 'DAY', 1), 'YYYYMMDD')
             add 1 to #weekend
             break
        when-other
             let $FD = dateToStr(dateAdd($FD, 'DAY', 1), 'YYYYMMDD')
    end-evaluate
end-while

The main take-away here is that you can use a date mask of 'D' to return the day number of the week. Sunday = 1, Saturday = 7, etc.