Solved

# Last Sunday of the Year

Posted on 2011-05-11
429 Views
Hi,

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

Using Oracle 10g

Thanks,

Iain
0
Question by:mullykid

LVL 23

Assisted Solution

- try this:

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

LVL 73

Accepted Solution

0

LVL 23

Expert Comment

- 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

LVL 73

Expert Comment

alternately

0

LVL 51

Expert Comment

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;
``````
0

LVL 73

Expert Comment

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

#### 745 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!