PLSQL - How to find Monday and Friday of the week of a given date

Question

I have spent days trying to figure this out to no avail, so hopefully someone can help me. I have a queried date set which contains several fields including a column of dates. What I want to do is create a new field in my query that tells what the Monday and Friday is for the week of that row's particular date.

So for example; if the date in one of my rows is "1/16/18", the new field should indicate "1/15/18 - 1/19/18".

So basically I need to be able to extract the Monday date (1/15/18) and the Friday date (1/19/18) of the week of 1/16/18 and then concatenate the two with a dash ( - ) in between. I need to do this for every row.

How on earth do I do this? I've been struggling just to figure out how to find the Monday or Friday of the given date...

Answer 1

Assuming that your column is of type date, you can use trunc to get the first day of the week (monday) and then add 4 days to get the friday.

For example:

with yourTable(d) as (select sysdate from dual)
select trunc(d, 'iw'), trunc(d, 'iw') + 4
from yourTable

To format the date as a string in the needed format, you can use to_char; for example:

with yourTable(d) as (select sysdate from dual)
select to_char(trunc(d, 'iw'), 'dd/mm/yy') ||'-'|| to_char(trunc(d, 'iw') + 4, 'dd/mm/yy')
from yourTable

gives

15/01/2018-19/01/18