檢查 vsql 沒有 dateadd 功能。
來自有 datetime.dt(year=x, week=y...) 的熊貓
在此處輸入自定義日期的替代方法是什么，以便可以實作以下目標
GETDATE() datediff(day=1, week=WEEK_ISO(GETDATE()) - 1)
uj5u.com熱心網友回復：
我剛剛有一個測驗程式，我根據 ISO 和標準計算一周的第一天，以及當月的第一天，以及 ISO 的最后一天和一系列“標準”周的最后一天日期。
這是完整的腳本及其結果 - 它還顯示了如何使用TIMESERIES來無中生有地創建日期串列：
WITH dtlimits(dt) AS (
SELECT DATE '2021-12-20'
UNION ALL SELECT DATE '2022-01-10'
)
,
dtlist AS (
SELECT
tsd::DATE AS dt
FROM dtlimits
TIMESERIES tsd AS '1 DAY' OVER(ORDER BY dt::TIMESTAMP)
)
SELECT
dt
, TO_CHAR(dt,'Dy') AS wkday
, dayofweek(dt)
, dayofweek_iso(dt)
, YEAR(dt)*100 WEEK(dt) AS yw
, YEAR(dt)*100 WEEK_ISO(dt) AS ywiso
, (dt - dayofweek_iso(dt) 1) AS firstdowiso
, (dt - dayofweek(dt) 1 ) AS firstdow
, ((dt - dayofweek(dt) 6 ) (86399/86400))::DATE AS lastdow
, ((dt - dayofweek_iso(dt) 6) (86399/86400))::DATE AS lastdowiso
, dayofmonth(dt) AS dom
FROM dtlist;
-- out dt | wkday | dayofweek | dayofweek_iso | yw | ywiso | firstdowiso | firstdow | lastdow | lastdowiso | dom
-- out ------------ ------- ----------- --------------- -------- -------- ------------- ------------ ------------ ------------ -----
-- out 2021-12-20 | Mon | 2 | 1 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 20
-- out 2021-12-21 | Tue | 3 | 2 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 21
-- out 2021-12-22 | Wed | 4 | 3 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 22
-- out 2021-12-23 | Thu | 5 | 4 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 23
-- out 2021-12-24 | Fri | 6 | 5 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 24
-- out 2021-12-25 | Sat | 7 | 6 | 202152 | 202151 | 2021-12-20 | 2021-12-19 | 2021-12-24 | 2021-12-25 | 25
-- out 2021-12-26 | Sun | 1 | 7 | 202153 | 202151 | 2021-12-20 | 2021-12-26 | 2021-12-31 | 2021-12-25 | 26
-- out 2021-12-27 | Mon | 2 | 1 | 202153 | 202152 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 27
-- out 2021-12-28 | Tue | 3 | 2 | 202153 | 202152 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 28
-- out 2021-12-29 | Wed | 4 | 3 | 202153 | 202152 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 29
-- out 2021-12-30 | Thu | 5 | 4 | 202153 | 202152 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 30
-- out 2021-12-31 | Fri | 6 | 5 | 202153 | 202152 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 31
-- out 2022-01-01 | Sat | 7 | 6 | 202201 | 202252 | 2021-12-27 | 2021-12-26 | 2021-12-31 | 2022-01-01 | 1
-- out 2022-01-02 | Sun | 1 | 7 | 202202 | 202252 | 2021-12-27 | 2022-01-02 | 2022-01-07 | 2022-01-01 | 2
-- out 2022-01-03 | Mon | 2 | 1 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 3
-- out 2022-01-04 | Tue | 3 | 2 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 4
-- out 2022-01-05 | Wed | 4 | 3 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 5
-- out 2022-01-06 | Thu | 5 | 4 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 6
-- out 2022-01-07 | Fri | 6 | 5 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 7
-- out 2022-01-08 | Sat | 7 | 6 | 202202 | 202201 | 2022-01-03 | 2022-01-02 | 2022-01-07 | 2022-01-08 | 8
-- out 2022-01-09 | Sun | 1 | 7 | 202203 | 202201 | 2022-01-03 | 2022-01-09 | 2022-01-14 | 2022-01-08 | 9
-- out 2022-01-10 | Mon | 2 | 1 | 202203 | 202202 | 2022-01-10 | 2022-01-09 | 2022-01-14 | 2022-01-15 | 10
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/447507.html
下一篇：計算兩個日期之間特定年份的日期數