First day of week (beginning with Sunday)

Question

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?

Answer 1

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)

Answer 2

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