CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

First day of week (beginning with Sunday)

I found that there is a function last_day for last day of month, and date_part(dow, date) for numeric day of week starting with Sunday, but I am trying to take a date, and get the first day of that week.

Meaning: if date='2018-02-14' then result should be '2018-02-11'.

Any ideas?

Upvotes: 7

Views: 28161

Answers (3)

Hoang Dinh
Hoang Dinh

Reputation: 306

If you want to get First day of week with week start day is any day in the week (Monday, Tuesday, ...). You can use this way:

day_of_week_index mapping:

{
    'monday': 1,
    'tuesday': 2,
    'wednesday': 3,
    'thursday': 4,
    'friday': 5,
    'saturday': 6,
    'sunday': 7
}

Query Template:

SELECT 
CASE
    WHEN extract(ISODOW FROM datetime_column) < day_of_week_index THEN cast(date_trunc('week', datetime_column) AS date) - 8 + day_of_week_index
    ELSE cast(date_trunc('week', datetime_column) AS date) - 1 + day_of_week_index
END
FROM table_name;

Example:

Get First day of week with week start day is Wednesday

SELECT
CASE
    WHEN extract(ISODOW FROM TIMESTAMP '2021-12-03 03:00:00') < 3 THEN cast(date_trunc('week', TIMESTAMP '2021-12-03 03:00:00') AS date) - 8 + 3
    ELSE cast(date_trunc('week', TIMESTAMP '2021-12-03 03:00:00') AS date) - 1 + 3
END;

=> Result:

2021-12-01

Note: You can change the day_of_week_index following the above mapping to determine the week start day (Monday, Tuesday, ..., Sunday).

Upvotes: 0

Matt Clark
Matt Clark

Reputation: 81

Not sure if there is a more efficient solution but: 

date_trunc('week',my_date + '1 day'::interval)::date - '1 day'::interval as week_start

Upvotes: 8

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

You simply want to subtract the dow value from the current date.

select dateadd(d, -datepart(dow, my_date), my_date)  
from (select date('2018-02-14') as my_date)

> 2018-02-11 00:00:00.0

For example, if dow is 3 for 2018-02-14 - a Wednesday - you can subtract 3 days to get back to "day 0".

There's also the date_trunc function which will truncate everything after a given datepart. This is a little clunky, and will only set you back to the previous Monday, not Sunday.

select date_trunc('week', my_date)
from (select date('2018-02-14') as my_date)

Upvotes: 14

Related Questions