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...

Solution

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

Comments

[OldProgrammer]

Please show sample data in a table format.

[mathguy]

The only ambiguity is the definition of "week" (and even then, there is an ambiguity only if the input date is a Saturday or a Sunday). Namely: is a week from Monday to Sunday? Then for a Sunday, the Monday of the same week precedes the input (the Sunday). But if a week starts on Sunday and ends the following Saturday, then "the Monday of the week of a Sunday" starts on the Monday following the Sunday. Please clarify.

[Andrew]

Aren't weeks Sunday - Saturday in SQL?

[William Robertson]

Looks like it's Oracle due to the "PLSQL" in the title, though admittedly that could mean anything. In which case 'IW' means the first day of the calendar week as defined by the ISO 8601 standard, which is Monday (quoting from Oracle 12.2 docs).

[Melanie]

Yes, I am coding in Oracle.

[Melanie]

So I took your suggestion and concatenated it, and this appears to work: , CONCAT(CONCAT(TRUNC(CALL_DATE, 'iw'),' - '),TRUNC(CALL_DATE, 'iw') + 4) as "Week Dates"

[Melanie]

But how do I get the format to be "MM/DD/YY"? Right now my result is looking like "01-JAN-18 - 05-JAN-18"