CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Lisa Tse
Lisa Tse

Reputation: 27

Using the next_day to retrieve the previous day and the following day

I have a sql code. I am trying to retrieve the previous Monday and next Sunday using the next_Day function. How would I make the query work? I am currently using oracle sql developer to run my query.

This is the data I want to show: 

 FullName,       Monday         Sunday
 John Brown      31-DEC-2012    06-JAN-2013

Here is my code I have so far:

 SELECT First_Name, Last_Name, DOB,
 Add_Months(dob, (extract(YEAR from sysdate)- extract(YEAR from dob))*12) AS BirthDateThisYear
 FROM Customers

I tried adding these statements, but I got an error after runninng the query: 

  NEXT_DAY(BirthDateThisYear-8, 'MON') AS PreviousMonday
  NEXT_DAY((BirthDateThisYear-8)+6, 'MON') AS UpcomingSunday

Upvotes: 0

Views: 1670

Answers (1)

Multisync
Multisync

Reputation: 8797

I can try this:

select First_Name || ' ' || Last_Name as FullName,
       NEXT_DAY(BirthDateThisYear-8, 'Monday') AS PreviousMonday,
       NEXT_DAY(BirthDateThisYear-1, 'Sunday') AS UpcomingSunday
from       
(SELECT First_Name, Last_Name, DOB,
 Add_Months(dob, (extract(YEAR from sysdate)- extract(YEAR from dob))*12) AS BirthDateThisYear
 FROM Customers)

Upvotes: 2

Related Questions