Home » SQL & PL/SQL » SQL & PL/SQL » Find Out first thursday for each month (oracle 10g)
Find Out first thursday for each month [message #577277] Wed, 13 February 2013 23:25 Go to next message
swapnil_naik
Messages: 268
Registered: December 2009
Location: Mumbai
Senior Member

How to find out first thursday for each month in year.
Re: Find Out first thursday for each month [message #577279 is a reply to message #577277] Wed, 13 February 2013 23:58 Go to previous messageGo to next message
sss111ind
Messages: 480
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;
Re: Find Out first thursday for each month [message #577281 is a reply to message #577279] Thu, 14 February 2013 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 19537
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>
Re: Find Out first thursday for each month [message #577288 is a reply to message #577279] Thu, 14 February 2013 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
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

Re: Find Out first thursday for each month [message #577336 is a reply to message #577279] Thu, 14 February 2013 04:48 Go to previous messageGo to next message
swapnil_naik
Messages: 268
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:


Re: Find Out first thursday for each month [message #577338 is a reply to message #577336] Thu, 14 February 2013 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
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
Re: Find Out first thursday for each month [message #577339 is a reply to message #577338] Thu, 14 February 2013 05:12 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

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

Regards,
Nathan
Re: Find Out first thursday for each month [message #577365 is a reply to message #577339] Thu, 14 February 2013 08:30 Go to previous message
Solomon Yakobson
Messages: 2010
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:
  


Current Time: Tue Sep 02 04:53:02 CDT 2014

Total time taken to generate the page: 0.13369 seconds