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

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. — , Jan 17 '18 at 00:45

Aleksej · Accepted Answer · 2018-01-16T20:18:17.947

4

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

edited Jan 16 '18 at 20:18

answered Jan 16 '18 at 19:14

Aleksej

22,443
5
33
38

Aren't weeks Sunday - Saturday in SQL? – Andrew Jan 16 '18 at 19:17
1

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](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ROUND-and-TRUNC-Date-Functions.html#GUID-8E10AB76-21DA-490F-A389-023B648DDEF8https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ROUND-and-TRUNC-Date-Functions.html) (quoting from Oracle 12.2 docs). – William Robertson Jan 16 '18 at 19:25
Yes, I am coding in Oracle. – Melanie Jan 16 '18 at 20:08
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 Jan 16 '18 at 20:09
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" – Melanie Jan 16 '18 at 20:12

shawnt00 · Answer 2 · 2018-01-16T19:46:16.617

There may be a simpler, canonical Oracle method to this but you can still reduce it to a simple calculation on your own either way. I'm going to assume you're dealing with only dates falling Monday through Friday. If you do need to deal with weekend dates then you might have to be more explicit about which logical week they should be attached to.

<date> - (to_char(<date>, 'D') - 2) -- Monday
<date> + (6 - to_char(<date>, 'D')) -- Friday

In principle all you need to do is add/subtract the appropriate number of days based on the current day of week (from 1 - 7). There are some implicit casts going on in there and it would probably be wise to handle those better. You might also want to check into NLS settings to make sure you can rely on to_char() using Sunday as the first day of week.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

score 0 · Answer 3 · answered Jan 17 '18 at 03:59

You can also use the NEXT_DAY function, as in:

SELECT TRUNC(NEXT_DAY(SYSDATE, 'MON')) - INTERVAL '7' DAY AS PREV_MONDAY,
       TRUNC(NEXT_DAY(SYSDATE, 'FRI'))                    AS NEXT_FRIDAY
  FROM DUAL;

Note that using the above, on weekends the Monday will be the Monday preceding the current date, and the Friday will be the Friday following the current date, i.e. there will be 11 days between the two days.

You can also use

SELECT TRUNC(NEXT_DAY(SYSDATE, 'MON')) - INTERVAL '7' DAY AS PREV_MONDAY,
       TRUNC(NEXT_DAY(SYSDATE, 'MON')) - INTERVAL '3' DAY AS NEXT_FRIDAY
  FROM DUAL;

in which case the Monday and Friday will always be from the same week, but if SYSDATE is on a weekend the Monday and Friday returned will be from the PREVIOUS week.

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

3 Answers3