How to Find out Next and Previous Day of Week in Oracle

Tips and Tricks

I have seen people writing number of lines of code to find out the date on a specific day in current or previous week, most often we need Friday the last working day of the week. We have a pretty simple way to find out in Oracle Queries.

Logic:

· Add a number of the day you want the date for in your current date, assuming Monday as day 1, Tuesday as day 2 and so on.

2	TRUNC(TO_DATE(SYSDATE,'YYYYMMDD') + 1, 'd')

Since Oracle considers Sunday as the first day of the week and Saturday as the last day adding 1 will shift this week frame one day back now week starts from Saturday and ends on Friday and TRUNC will give the currents weeks Sunday.

·    Now whatever day you want in the week just add or subtract the number of day from the Sunday you got. In our case we are looking for Friday so we add 5 since it is the 5 day of our new week. 

2	TRUNC(TO_DATE(SYSDATE,'YYYYMMDD') + 1, 'd') + 5

So our Query will look something like this

1	SELECT TRUNC(TO_DATE(SYSDATE,'YYYYMMDD') + 1, 'd') + 5

2	FROM DUAL;

3

EXAMPLES:

Query for Next Friday:

SELECT TRUNC(TO_DATE('20140705','YYYYMMDD') + 1, 'd') + 4 AS SAT,                     TRUNC(TO_DATE('20140706','YYYYMMDD') + 1, 'd') + 4 AS SUN,                     TRUNC(TO_DATE('20140707','YYYYMMDD') + 1, 'd') + 4 AS MON,                     TRUNC(TO_DATE('20140708','YYYYMMDD') + 1, 'd') + 4 AS TUE,                     TRUNC(TO_DATE('20140709','YYYYMMDD') + 1, 'd') + 4 AS WED,                     TRUNC(TO_DATE('20140710','YYYYMMDD') + 1, 'd') + 4 AS THU,                     TRUNC(TO_DATE('20140711','YYYYMMDD') + 1, 'd') + 4 AS FRI

FROM DUAL;

Output:

Query for Previous Friday: 

SELECT TRUNC(TO_DATE('20140705','YYYYMMDD') + 1, 'd') + 4 AS SAT,                     TRUNC(TO_DATE('20140706','YYYYMMDD') + 1, 'd') + 4 AS SUN,                     TRUNC(TO_DATE('20140707','YYYYMMDD') + 1, 'd') + 4 AS MON,                     TRUNC(TO_DATE('20140708','YYYYMMDD') + 1, 'd') + 4 AS TUE,                     TRUNC(TO_DATE('20140709','YYYYMMDD') + 1, 'd') + 4 AS WED,                     TRUNC(TO_DATE('20140710','YYYYMMDD') + 1, 'd') + 4 AS THU,                     TRUNC(TO_DATE('20140711','YYYYMMDD') + 1, 'd') + 4 AS FRI

FROM DUAL;

Output: