給定 sqllite 中的日期，我想找到該月的最后一個作業日。例如，如果給定 11/17/2021，則該月的最后一個作業日是 11/30。但是，如果給定 2022 年 4 月 30 日是星期六，則該月的最后一個作業日是 2022 年 4 月 29 日。
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');
我正在尋找在 sql lite 中給定特定日期的月份的最后一個作業日。有人可以幫我解決這個問題嗎？提前致謝
uj5u.com熱心網友回復：
一種解決方案是計算月末的星期幾，并根據該值調整結果：
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;
uj5u.com熱心網友回復：
以下將采用美國格式的日期 (m/d/y) 并以相同的美國格式輸出該月的最后一個作業日：-
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
;
例如
- 對于二零二一年十一月十七日則： -
- 對于2022年4月30日則： -
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/360086.html
標籤：sqlite