CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
user14328853
user14328853

Reputation: 434

Get week beginning date

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?

Upvotes: 0

Views: 2507

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

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

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10134

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

Upvotes: 0

Related Questions