checked that vsql doesn't have dateadd function.
Coming from pandas where there's datetime.dt(year=x, week=y...)
What's the alternative to input a custom date here so that the below can be achieved
GETDATE() datediff(day=1, week=WEEK_ISO(GETDATE()) - 1)
CodePudding user response：
I just had a test program up my sleeve, where I calculate the first day of the week according to ISO and according to standard, and of the month, and also the last day of the ISO and of the "standard" week for a series of dates.
Here's the full script and its result - it also shows the use of TIMESERIES to create a list of dates out of nothing:
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