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?
3 Answers
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) 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 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).
