Last Sunday of the Year

Hi,

Can someone tell me (in PL/SQL) how to retrieve the last sunday (for example) of the year.

Using Oracle 10g

Thanks,

Iain
mullykidAsked:
Who is Participating?
 
sdstuberCommented:
next_day(add_months(trunc(sysdate,'yyyy'),12)-8,'Sunday')
0
 
OP_ZaharinCommented:
- try this:

SELECT next_day((add_months(trunc(sysdate,'YEAR'), 12) - 1 )- 7,'SUN') FROM dual;

Open in new window

0
 
OP_ZaharinCommented:
- to explain:
: to find last day of the year: (add_months(trunc(sysdate,'YEAR'),12) - 1)
: to find last Sunday of the week: next_day(sysdate - 7,'SUN')
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
sdstuberCommented:
alternately

next_day(add_months(trunc(sysdate,'yyyy'),11)+23,'Sunday')
0
 
HainKurtSr. System AnalystCommented:
here it is (replace systdate with your variable on second line)
declare 
nextyear date := to_date(to_char(to_number(to_char(sysdate,'yyyy'))+1) || '0101', 'yyyymmdd');
lastSunday date;
begin

dbms_output.put_line(nextyear);
dbms_output.put_line(to_char(nextyear,'d'));
lastSunday  := nextyear - to_number(to_char(nextyear,'d'));
dbms_output.put_line(lastSunday);

end;

Open in new window

0
 
sdstuberCommented:
I recommend not using date-to-string-to-number-to-string-to-date conversions in addition to date math.  Just use date math.

The "trick" is you want to find December 24th of the year.

Why?  Because that's one week from the end of the year.

Once you have the 24th,  NEXT_DAY for any day of the week will be the last of that day for the year.

So, you can either go to Jan 1st of the following year and backup 8 days
or you can go to Dec 1st of the current year and add 23 days

Either way you get to Dec 24 and then let next_day do its thing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.