Introduction:
I have been searching for a blog or post to provide a solution to get Weekstart date and Weekend date from Week number in Redshift. There was none. So I tried myself and writing this.
Requirement:
I have a week number of year, say 42 and year 2020. I want to find the week start date and week end date for that week. This needs to be achieved in Amazon Redshift.
Example,
Solution: (Assuming Week starts from Sunday to Saturday)
select trunc(to_date(weekNum||' '||year,'WW YYYY'))-(date_part(dow,trunc(to_date(weekNum||' '||year,'WW YYYY'))))::integer weekStartDate,
(trunc(to_date(weekNum||' '||year,'WW YYYY'))-(date_part(dow,trunc(to_date(weekNum||' '||year,'WW YYYY'))))::integer)+6 weekEndDate
from(select 42 as weekNum, 2020 as year);
Let us check this in detail.
The below select is a static SQL to provide data.
select 42 as weekNum, 2020 as year
Following is the major part,
trunc(to_date(weekNum||' '||year,'WW YYYY'))-(date_part(dow,trunc(to_date(weekNum||' '||year,'WW YYYY'))))::integer
Let us split this further and discuss,
1. to_date(weekNum||' '||year,'WW YYYY')
This to_date function will take varchar input and output as the specified date format. In the above query, the date is generated based on the weekNum and year with format as WW(Week) YYYY(year).
Sample:
select to_date(42||' '||2020,'WW YYYY');
2. date_part(dow,trunc(to_date(weekNum||' '||year,'WW YYYY')))
date_part function will get or extract the part from date. For example, we can fetch the month from date like date_part(m,'2020-10-14'). The output will be 10.
Sample:
select date_part(dow,'2020-10-14');
3. trunc(to_date(weekNum||' '||year,'WW YYYY'))-(date_part(dow,trunc(to_date(weekNum||' '||year,'WW YYYY'))))::integer
Sample:
Conclusion:
So the final output will be 2020-10-14 minus 3 which is 2020-10-11 that is Sunday
