Get week beginning date

Question

I would like to get the week beginning Monday's for a range of dates. I read over this SO post on how to do that and tried with

select 
    id,
    install_date, -- leave it in to check and validate manually
    cast(dateadd(day, -1*(DATEPART(WEEKDAY, install_date)-2), install_date) as DATE) as WeekStart
from someschema.sometable

This gives error:

SQL compilation error: error line 4 at position 32 invalid identifier 'WEEKDAY'

How can I get the week beginning Monday for each date in someschema.sometable.install_date? I.e. I'd like to eventually group by weeks where weeks run Monday to Sunday and display as the Monday week beginning?

I think your link SO post is not the correct one. – Simeon Pilgrim Mar 23 '21 at 20:03 — Simeon Pilgrim, Mar 23 '21 at 20:03
@SimeonPilgrim thanks for catching that, have updated – user14328853 Mar 23 '21 at 20:08 — user14328853, Mar 23 '21 at 20:08

score 1 · Answer 1 · answered Mar 23 '21 at 20:10

1

The simplest form is to use part_trunc and truncate to 'week' using week_start 1

select column1
    ,date_trunc('week', column1) as week_start
from values 
   ('2021-03-24'::date), 
   ('2021-03-15'::date);

gives:

COLUMN1     WEEK_START
2021-03-24  2021-03-22
2021-03-15  2021-03-15

answered Mar 23 '21 at 20:10

Simeon Pilgrim

22,906
3
32
45

Thanks @simeon but I preferred the syntax of LAST_DAY in Gokhan's answer – user14328853 Mar 24 '21 at 13:35
@user14328853 wow, you want the start of the week, and there is a function that does exactly that, and you prefer to the the last day of the week and then do math, because the double indirection is "simpler to understand".... I see, you are welcome to award the answer you who you like, but I really think your SQL is worse for the next person with unneeded indirection. – Simeon Pilgrim Mar 24 '21 at 20:22

score 0 · Accepted Answer · answered Mar 23 '21 at 14:40

The error is about the function name (DATE_PART instead of DATEPART) but I think you should also improve the calculation:

with sometable as (
select 1 id, '2021-03-23'::date install_date )
select 
    id,
    install_date, -- leave it in to check and validate manually
    cast(dateadd(day, -1*(DATE_PART(WEEKDAY, install_date)-2), install_date) as DATE) as WeekStart
from sometable;

I would use LAST_DAY to calculate the starting of the week:

with sometable as (
select 1 id, '2021-03-23'::date install_date )
select 
    id,
    install_date, -- leave it in to check and validate manually
    LAST_DAY(install_date, week) - 6 as WeekStart 
from sometable;

LAST_DAY https://docs.snowflake.com/en/sql-reference/functions/last_day.html

Thanks, this runs successfully but seems to return weeks beginning on Tuesdays and not Mondays! I changed the -2 to -1 and that seems to have done it. Thanks! — user14328853, Mar 23 '21 at 14:49

Get week beginning date

2 Answers2