Home » SQL & PL/SQL » SQL & PL/SQL » How to find last sunday of month
How to find last sunday of month [message #605755] Tue, 14 January 2014 06:26 Go to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Hi Guys,

Please let me know that, how to find last Sunday of month, suppose we put "03-Jan-2013" how to find last Sunday.

please write oracle query.

Report message to a moderator

Re: How to find last sunday of month [message #605756 is a reply to message #605755] Tue, 14 January 2014 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something like:
SQL> select next_day(to_date('03-Jan-2013','DD-mon-YYYY'),'Sunday')-7 last_sunday from dual;
LAST_SUNDAY
-----------
30-DEC-2012

Now you have to define what is the last Sunday when you are on Sunday.

Report message to a moderator

Re: How to find last sunday of month [message #605757 is a reply to message #605756] Tue, 14 January 2014 06:47 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does "last Sunday" mean?
- The last Sunday prior 03-Jan-2013 (that would be Michel's answer)
- The last Sunday in January 2013 (it was 27-Jan-2013)
- Something else

[Updated on: Tue, 14 January 2014 06:48]

Report message to a moderator

Re: How to find last sunday of month [message #605758 is a reply to message #605757] Tue, 14 January 2014 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the latter (waiting for a batch to end...):
SQL> select next_day(last_day(to_date('03-Jan-2013','DD-mon-YYYY')),'Sunday')-7 last_sunday from dual;
LAST_SUNDAY
-----------
27-JAN-2013

Report message to a moderator

Re: How to find last sunday of month [message #605759 is a reply to message #605756] Tue, 14 January 2014 06:58 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Thanks u very much Michel,

[Updated on: Tue, 14 January 2014 06:58]

Report message to a moderator

Re: How to find last sunday of month [message #605765 is a reply to message #605755] Tue, 14 January 2014 07:52 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
aaditya321 wrote on Tue, 14 January 2014 07:26
Please let me know that, how to find last Sunday of month.


You could use NEXT_DAY as others suggested:

next_day(last_day(dt) - 7,'Sunday')


For example:

SCOTT@pdborcl12 > with t as (
  2             select  add_months(sysdate,level - 1) dt
  3               from dual
  4               connect by level <= 12
  5            )
  6  select  to_char(dt,'fmMonth, YYYY') month,
  7          to_char(next_day(last_day(dt) - 7,'Sunday'),'fmMonth DD, YYYY') last_sunday
  8    from  t
  9    order by dt
 10  /

MONTH           LAST_SUNDAY
--------------- ------------------
January, 2014   January 26, 2014
February, 2014  February 23, 2014
March, 2014     March 30, 2014
April, 2014     April 27, 2014
May, 2014       May 25, 2014
June, 2014      June 29, 2014
July, 2014      July 27, 2014
August, 2014    August 31, 2014
September, 2014 September 28, 2014
October, 2014   October 26, 2014
November, 2014  November 30, 2014

MONTH           LAST_SUNDAY
--------------- ------------------
December, 2014  December 28, 2014

12 rows selected.

SCOTT@pdborcl12 >


However, using NEXT_DAY results in NLS-dependent solution. Therefore it will fail for a client, for example, in France:

SCOTT@pdborcl12 > alter session set nls_language=french
  2  /

Session altered.

SCOTT@pdborcl12 > with t as (
  2             select  add_months(sysdate,level - 1) dt
  3               from dual
  4               connect by level <= 12
  5            )
  6  select  to_char(dt,'fmMonth, YYYY') month,
  7          to_char(next_day(last_day(dt) - 7,'Sunday'),'fmMonth DD, YYYY') last_sunday
  8    from  t
  9    order by dt
 10  /
        to_char(next_day(last_day(dt) - 7,'Sunday'),'fmMonth DD, YYYY') last_sunday
                                          *
ERROR at line 7:
ORA-01846: ce n'est pas un jour de semaine valide


SCOTT@pdborcl12 >


Using TRUNC with IW format makes it NLS-independent:

SCOTT@pdborcl12 > with t as (
  2             select  add_months(sysdate,level - 1) dt
  3               from dual
  4               connect by level <= 12
  5            )
  6  select  to_char(dt,'fmMonth, YYYY') month,
  7          to_char(trunc(last_day(dt) + 1,'IW') - 1,'fmMonth DD, YYYY') last_sunday
  8    from  t
  9    order by dt
 10  /

MONTH           LAST_SUNDAY
--------------- ------------------
Janvier, 2014   Janvier 26, 2014
Fevrier, 2014   Fevrier 23, 2014
Mars, 2014      Mars 30, 2014
Avril, 2014     Avril 27, 2014
Mai, 2014       Mai 25, 2014
Juin, 2014      Juin 29, 2014
Juillet, 2014   Juillet 27, 2014
Aout, 2014      Aout 31, 2014
Septembre, 2014 Septembre 28, 2014
Octobre, 2014   Octobre 26, 2014
Novembre, 2014  Novembre 30, 2014

MONTH           LAST_SUNDAY
--------------- ------------------
Decembre, 2014  Decembre 28, 2014

12 rows selected.

SCOTT@pdborcl12 > alter session set nls_language=american
  2  /

Session altered.

SCOTT@pdborcl12 > with t as (
  2             select  add_months(sysdate,level - 1) dt
  3               from dual
  4               connect by level <= 12
  5            )
  6  select  to_char(dt,'fmMonth, YYYY') month,
  7          to_char(trunc(last_day(dt) + 1,'IW') - 1,'fmMonth DD, YYYY') last_sunday
  8    from  t
  9    order by dt
 10  /

MONTH           LAST_SUNDAY
--------------- ------------------
January, 2014   January 26, 2014
February, 2014  February 23, 2014
March, 2014     March 30, 2014
April, 2014     April 27, 2014
May, 2014       May 25, 2014
June, 2014      June 29, 2014
July, 2014      July 27, 2014
August, 2014    August 31, 2014
September, 2014 September 28, 2014
October, 2014   October 26, 2014
November, 2014  November 30, 2014

MONTH           LAST_SUNDAY
--------------- ------------------
December, 2014  December 28, 2014

12 rows selected.

SCOTT@pdborcl12 >


SY.

Report message to a moderator

Previous Topic: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats
Next Topic: ORA-01481: invalid number format model
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 12:52:15 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.