OraFAQ Forum: SQL & PL/SQL » Find Out first thursday for each month

Home » SQL & PL/SQL » SQL & PL/SQL » Find Out first thursday for each month (oracle 10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Find Out first thursday for each month [message #577277]

Wed, 13 February 2013 23:25

swapnil_naik
Messages: 267
Registered: December 2009
Location: Mumbai

Senior Member

How to find out first thursday for each month in year.

Report message to a moderator

Re: Find Out first thursday for each month [message #577279 is a reply to message #577277]

Wed, 13 February 2013 23:58

sss111ind
Messages: 267
Registered: April 2012
Location: India

Senior Member

SELECT next_day(to_date('01-'
  ||LEVEL
  ||'-2013','dd-mm-yyyy'),'thursday') first_thursday
FROM dual
  CONNECT BY LEVEL<=12;

Report message to a moderator

Re: Find Out first thursday for each month [message #577281 is a reply to message #577279]

Thu, 14 February 2013 00:12

Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test as
  2    (select trunc(sysdate, 'yyyy') + level - 1 datum
  3     from dual
  4     connect by level <=
  5       add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy')
  6    )
  7  select min(datum) the_first_thursday
  8  from test
  9  where trim(to_char(datum, 'day', 'nls_date_language=English')) = 'thursday'
 10  group by trunc(datum, 'mm')
 11  order by 1;

THE_FIRST_
----------
03.01.2013
07.02.2013
07.03.2013
04.04.2013
02.05.2013
06.06.2013
04.07.2013
01.08.2013
05.09.2013
03.10.2013
07.11.2013
05.12.2013

12 rows selected.

SQL>

Report message to a moderator

Re: Find Out first thursday for each month [message #577288 is a reply to message #577279]

Thu, 14 February 2013 01:07

Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

sss111ind wrote on Thu, 14 February 2013 06:58

SELECT next_day(to_date('01-'
  ||LEVEL
  ||'-2013','dd-mm-yyyy'),'thursday') first_thursday
FROM dual
  CONNECT BY LEVEL<=12;

Wrong! See august:

SQL> SELECT next_day(to_date('01-'
  2    ||LEVEL
  3    ||'-2013','dd-mm-yyyy'),'thursday') first_thursday
  4  FROM dual
  5    CONNECT BY LEVEL<=12;
FIRST_THURS
-----------
03-JAN-2013
07-FEB-2013
07-MAR-2013
04-APR-2013
02-MAY-2013
06-JUN-2013
04-JUL-2013
08-AUG-2013
05-SEP-2013
03-OCT-2013
07-NOV-2013
05-DEC-2013

It should be 1st August.
One correct version is:

SQL> select next_day(add_months(trunc(sysdate,'YEAR')-1,level-1),'THU') 
  2  from dual
  3  connect by level <= 12
  4  /
NEXT_DAY(AD
-----------
03-JAN-2013
07-FEB-2013
07-MAR-2013
04-APR-2013
02-MAY-2013
06-JUN-2013
04-JUL-2013
01-AUG-2013
05-SEP-2013
03-OCT-2013
07-NOV-2013
05-DEC-2013

Regards
Michel

Report message to a moderator

Re: Find Out first thursday for each month [message #577336 is a reply to message #577279]

Thu, 14 February 2013 04:48

swapnil_naik
Messages: 267
Registered: December 2009
Location: Mumbai

Senior Member

Hey Thanks Buddy...

But the problem is, suppose year is 2012, on that year Mar 2012 - First thurday come in 01-mar-2012.

As per your query,

 SELECT next_day(to_date('01-'
  ||LEVEL
  ||'-2012','dd-mm-yyyy'),'thursday') first_thursday
FROM dual
  CONNECT BY LEVEL<=12;

O/P Come
Quote:

Report message to a moderator

Re: Find Out first thursday for each month [message #577338 is a reply to message #577336]

Thu, 14 February 2013 04:57

Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe you should read my post!
(By the way and use the Preview button to read yours.)

Regards
Michel

Report message to a moderator

Re: Find Out first thursday for each month [message #577339 is a reply to message #577338]

Thu, 14 February 2013 05:12

sss111ind
Messages: 267
Registered: April 2012
Location: India

Senior Member

Thanks Michel,LittleFoot for wonedrful solutions and correcting the query.

Regards,
Nathan

Report message to a moderator

Re: Find Out first thursday for each month [message #577365 is a reply to message #577339]

Thu, 14 February 2013 08:30

Solomon Yakobson
Messages: 1397
Registered: January 2010

Senior Member

NLS-independent solution:

with test as (
              select  add_months(trunc(sysdate,'YEAR'),level-1) datum
                from  dual
                connect by level <= 12
             )
select  trunc(datum + 3,'iw') + 3 -- 3 is # of days between Thursday and Monday
  from  test
  order by 1
/

TRUNC(DATUM
-----------
03-JAN-2013
07-FEB-2013
07-MAR-2013
04-APR-2013
02-MAY-2013
06-JUN-2013
04-JUL-2013
01-AUG-2013
05-SEP-2013
03-OCT-2013
07-NOV-2013

TRUNC(DATUM
-----------
05-DEC-2013

12 rows selected.

SQL>

SY.

[Updated on: Thu, 14 February 2013 08:30]

Report message to a moderator

Previous Topic:	Execute Immediate
Next Topic:	External Tables (Oracle Loader)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 01:50:01 CDT 2013

Total time taken to generate the page: 0.19096 seconds