Issue
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)
Solution
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
Answered By – marcothesane
Answer Checked By – Gilberto Lyons (BugsFixing Admin)