How to change start of the week in PostgreSQL

Question

I'm trying to create a string with the week number and the first and last date on that week, like this:

'W41 04/10-10/10'

I was able to create a formula on Google Sheets that solve my problem and now I need to do this on PostgreSQL.

The problem is we use Sunday as the first day of the week on our reports and PostgreSQL uses Monday as the first day.

So for PostgreSQL Week 41 is from 05/10 to 11/10 and I need it from 04/10 to 10/10

My question is: Is there a way to change the first day on PostgreSQL too? I've created a code that creates the string just like I need, but the week number is wrong because of that definition.

Edit:

I've managed to correct the week number using this code:

ceil(((cte.data::date -((CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date - CAST
               (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date)
               as integer)
               ))+1)/7.0)

Now the week number appears just like i need, for example, 04/10 is Week 41 Now I need to get first and last date based on this, the result this far is:

Answer 1

After some research and thinking I used this code to get the week number like I needed

ceil(((cte.data::date -((CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date - CAST
               (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date)
               as integer)
               ))+1)/7.0)

and then I gave an alias of 'num' and calculated the first and last date this way:

'W' || num ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-10,' DD/MM - ')  ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-4, 'DD/MM') as WEEK

The final output was the week number with start and end date of the week using Sunday as the first day in the week

Answer 2

You can use DATE_TRUNC along with TO_CHAR function

SELECT TO_CHAR(current_date, '"W"IW ')|| 
       TO_CHAR(DATE_TRUNC('week',current_date),'DD/MM - ')||
       TO_CHAR(DATE_TRUNC('week',current_date)+ INTERVAL '6 day','DD/MM')

and change the added values within + INTERVAL 'c day'(in this case c=0,which is implicit, and c=6) so as the difference between those constants will be 6 if you want to change the first/last days of week in the result.

Demo