how to get the last weekday of the month in sqllite-CodePudding

given a date in sqllite I would like to find the last weekday of the month. for example, if given 11/17/2021 then the last weekday of the month is 11/30. however, if given 4/30/2022 which falls on a saturday then the last weekday of the month is 4/29/2022.

i try the following but this only gives me the last day of the month which can be either a weekend of weeday.
SELECT date('now','start of month',' 1 month','-1 day');

i am looking for the last weekday of the month given a specific date in sql lite. can someone help me figure this out? thanks in advance

CodePudding user response：

One solution is to calculate the day of the week for the end of the month, and adjust the result based off that value:

SELECT 
    CASE strftime('%w', date('now', 'start of month',' 1 month','-1 day'))
        WHEN '0' THEN date('now', 'start of month',' 1 month','-3 day')
        WHEN '6' THEN date('now', 'start of month',' 1 month','-2 day')
        ELSE date('now', 'start of month',' 1 month','-1 day') 
    END;

CodePudding user response：

The following will take a US formatted date (m/d/y) and output the last weekday in the month in the same US format:-

WITH
    cte_inputdate AS (SELECT '12/19/2022' /*<<<<<<<<<<< change as required */ AS inputdate),
    cte_convertdate AS 
        (SELECT 
            CASE
            /* m/d/yyyy */
            WHEN substr(inputdate,2,1) = '/' 
                AND substr(inputdate,4,1) = '/'
                THEN substr(inputdate,5,4)||'-0'||substr(inputdate,1,1)||'-0'||substr(inputdate,3,1)
            /* m/dd/yyyy */
            WHEN substr(inputdate,2,1) = '/'
                AND substr(inputdate,5,1) = '/'
                THEN substr(inputdate,6,4)||'-0'||substr(inputdate,1,1)||'-'||substr(inputdate,3,2)
            /* mm/d/yyyy */
            WHEN substr(inputdate,3,1) = '/'
                AND substr(inputdate,5,1) = '/'
                THEN substr(inputdate,6,4)||'-'||substr(inputdate,1,2)||'-0'||substr(inputdate,4,1)
            /* mm/dd/yyyy */    
            ELSE substr(inputdate,7,4)||'-'||substr(inputdate,1,2)||'-'||substr(inputdate,4,2)
            END AS inputdate
        FROM cte_inputdate
        ),
    cte_lastweekdayofmonth AS
        (SELECT *,
            CASE CAST(strftime('%w',inputdate,'start of month',' 1 month','-1 day') AS INTEGER)
                WHEN 0 THEN date(inputdate,'start of month',' 1 month','-3 day')
                WHEN 6 THEN date(inputdate,'start of month',' 1 month','-2 day')
                ELSE date(inputdate,'start of month',' 1 month','-1 day')
                END AS lastweekdayofmonth
        FROM cte_convertdate
        )
/* Extract lastweekdayofthemonth converting it to m/d/yyyy format */
SELECT
    CASE WHEN substr(lastweekdayofmonth,6,1) = '0' THEN substr(lastweekdayofmonth,7,1) ELSE substr(lastweekdayofmonth,6,2) END||'/'||
    CASE WHEN substr(lastweekdayofmonth,9,1) = '0' THEN substr(lastweekdayofmonth,10,1) ELSE substr(lastweekdayofmonth,9,2) END||'/'||
    substr(lastweekdayofmonth,1,4) AS lastweekdayofmonth
FROM cte_lastweekdayofmonth
;

e.g

for 11/17/2021 then :-

for 4/30/2022 then :-