I've got a special request for a new project. We've got a date like "2022-02-01" and we have to calculate it to get the following string as result "WB - Jan-31". It has to be always the date of the first day of the week. First day of week is Monday. A colleague give the hint to solve it with the to_char () function, but how do I manage to get a result like this?

What I have right now:

This gives a number of day of the week back as integer for calculation.

SELECT create_date, to_char(create_date, 'ID')::integer as dow
FROM customer;

I also figured how to produce a string.

SELECT create_date, to_char(create_date, '"WB - " Mon DD')
FROM customer;

What I don't know how to mange to get as result the first day of week for Monday to Friday like "WS - Jan 31"?

I also figured how to calculate first day of week as date but how put it in this result?

SELECT create_date - to_char(create_date, 'ID')::integer   1
FROM customer;

I appreciate every help, I'm still into learning SQL from scratch and it's my first week of doing this at my new position.

CodePudding user response：

To get the start of the week from a DATE value, use date_trunct() the result can then be formatted using to_char()

to_char(date_trunc('week', create_date), 'Mon DD')

You can concatenate any prefix you want to the result of that, e.g.

concat('WS - ', to_char(date_trunc('week', create_date), 'Mon DD'))

CodePudding user response：

Assuming the WS is week starting , and WB is week beginning, so no real issue, try this:

WITH
indata(dt,wk) AS (
          SELECT DATE '2022-02-01','WS - Jan 31'
UNION ALL SELECT DATE '2022-02-02','WS - Jan 31'
UNION ALL SELECT DATE '2022-02-03','WS - Jan 31'
UNION ALL SELECT DATE '2022-02-04','WS - Jan 31'
UNION ALL SELECT DATE '2022-02-05','WS - Jan 31'
UNION ALL SELECT DATE '2022-02-07','WS - Feb 07'
UNION ALL SELECT DATE '2022-02-08','WS - Feb 07'
UNION ALL SELECT DATE '2022-02-09','WS - Feb 07'
UNION ALL SELECT DATE '2022-02-10','WS - Feb 07'
UNION ALL SELECT DATE '2022-02-11','WS - Feb 07'
UNION ALL SELECT DATE '2022-02-12','WS - Feb 07'
)
SELECT
  *
, EXTRACT(DOW FROM dt)
, 'WS - ' || TO_CHAR(dt - EXTRACT(DOW FROM dt)    2, 'Mon DD') AS wk_calc
FROM indata
ORDER BY 1;
-- out      dt     |     wk      | date_part |   wk_calc   
-- out ------------ ------------- ----------- -------------
-- out  2022-02-01 | WS - Jan 31 |         2 | WS - Feb 01
-- out  2022-02-02 | WS - Jan 31 |         3 | WS - Feb 01
-- out  2022-02-03 | WS - Jan 31 |         4 | WS - Feb 01
-- out  2022-02-04 | WS - Jan 31 |         5 | WS - Feb 01
-- out  2022-02-05 | WS - Jan 31 |         6 | WS - Feb 01
-- out  2022-02-07 | WS - Feb 07 |         1 | WS - Feb 08
-- out  2022-02-08 | WS - Feb 07 |         2 | WS - Feb 08
-- out  2022-02-09 | WS - Feb 07 |         3 | WS - Feb 08
-- out  2022-02-10 | WS - Feb 07 |         4 | WS - Feb 08
-- out  2022-02-11 | WS - Feb 07 |         5 | WS - Feb 08
-- out  2022-02-12 | WS - Feb 07 |         6 | WS - Feb 08
-- out (11 rows)