While executing the stored procedure It is not giving expected output

Question

CREATE OR REPLACE PROCEDURE "internal"."get_last_weekday_of_month"(AsofDate TIMESTAMP_NTZ(9))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS
$$
DECLARE
LastWeekDay TIMESTAMP_NTZ(9);
BEGIN
truncate table "internal"."monthend_process";
LastWeekDay := DATEADD(DAY,CASE DAYOFWEEK(AsofDate)
                               WHEN 6 THEN -1
                               WHEN 7 THEN -2
                               ELSE 0
                           END, AsofDate);

INSERT INTO "internal"."monthend_process" ("MonthEndDates","created_on", "RollOverStartDate",         "PrevPrevMonthEnd")
SELECT :LastWeekDay as "MonthEndDates", CURRENT_TIMESTAMP(), NULL,NULL
UNION
SELECT LAST_DAY(:AsofDate) as "MonthEndDates", CURRENT_TIMESTAMP(), NULL,NULL ;

UPDATE "internal"."monthend_process"
SET "RollOverStartDate" = DATEADD(DAY, -4, :AsofDate),
"PrevPrevMonthEnd" = LAST_DAY(DATEADD(MONTH, -1, :AsofDate));

  RETURN 'Stored procedure executed';
     END;
  $$;

This stored procedure is working fine, but when I am passing any Sunday date, then it is not returning Friday's date, and for Saturday, it is working fine it is returning Friday date.

Answer 1

I believe if you changed the following:

LastWeekDay := DATEADD(DAY,CASE DAYOFWEEK(AsofDate)
                               WHEN 6 THEN -1
                               WHEN 7 THEN -2
                               ELSE 0
                           END, AsofDate);

To the below it should work as you expected:

LastWeekDay := DATEADD(DAY,CASE DAYOFWEEK(AsofDate)
                               WHEN 6 THEN -1
                               WHEN 0 THEN -2
                               ELSE 0
                           END, AsofDate);