CODEWITHSUNDEEP
sqloracle-database
rcmuthu786
rcmuthu786

Reputation: 130

How to get last thursday of every month in a year 2013 in oracle?

How to get last Thursday of every month in a year 2013 in oracle? i need to update this date into my table.

i need a output like 

Last Thursday in a year 2013 
----------------------
31.01.2013
28.02.2013
28.03.2013
24.04.2013
30.05.2013
27.06.2013
25.07.2013
29.08.2013
26.09.2013
31.10.2013
28.11.2013
26.12.2013

Thanks to do the needful.

Upvotes: 0

Views: 4102

Answers (4)

Tony Andrews
Tony Andrews

Reputation: 132570

This will do it:

select next_day (last_day (add_months(date '2013-01-01', rownum-1))-7, 'THU') as thurs
from dual
connect by level <= 12;

THURS
---------
31-JAN-13
28-FEB-13
28-MAR-13
25-APR-13
30-MAY-13
27-JUN-13
25-JUL-13
29-AUG-13
26-SEP-13
31-OCT-13
28-NOV-13
26-DEC-13

12 rows selected.

Explanation:

1) The following select is a way to generate a series of integers 1..12:

select rownum from dual connect by level <= 12;

2) This returns the 1st of each of the 12 months of 2012 by taking 1st January 2013 and adding 0 months, 1 month, ..., 11 months:

select add_months(date '2013-01-01', rownum-1)
from dual connect by level <= 12;

3) The last_day function returns the last day of the month for the given date, so that we now have 2013-01-31, 2013-02-28, ..., 2013-12-31.

4) next_day (date, 'THU') returns the next Thursday after the specified date. To get the last Thursday of the month we take the last day of the month, go back 7 days, then find the next Thursday.

Upvotes: 12

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40499

I'd go with dbms_scheduler:

declare
  start_dt              date := date '2013-01-01';
  months_last_thursday  date;

begin

  loop

    dbms_scheduler.evaluate_calendar_string (

       calendar_string   => 'FREQ=MONTHLY;BYDAY=-1 THU',
       start_date        =>  start_dt,
       return_date_after =>  start_dt, 
       next_run_date     =>  months_last_thursday

    );

    exit when months_last_thursday > date '2013-12-31';

    dbms_output.put_line(months_last_thursday);

    start_dt := months_last_thursday;

  end loop;
end;
/

Upvotes: 4

fabribara
fabribara

Reputation: 140

The most elegant way is the following

select thdate from (
select a.*, row_number() over (partition by to_char(thdate,'yyyymm'),day order by thdate desc) row_rank
 from (
select trunc(sysdate,'YEAR') + rownum-1 thdate,
trim(to_char(trunc(sysdate,'YEAR') + rownum-1,'DAY')) day from dba_objects 
where trunc(sysdate,'YEAR') + rownum-1 <
trunc(sysdate+365,'YEAR')
) a where day='THURSDAY'
) where row_rank=1

Upvotes: -1

wolφi
wolφi

Reputation: 8361

Building on @Rene's answer, this could be extended into a general (generic?) solution:

CREATE OR REPLACE FUNCTION recurring_dates (p_year IN NUMBER, p_rule IN VARCHAR) 
  RETURN dbms_stats.datearray PIPELINED
AS
  start_dt DATE;
  last_dt  DATE;
  next_dt  DATE;
BEGIN
  start_dt := to_date(to_char(p_year,'fm0000')||'01-01','YYYY-MM-DD');
  last_dt  := to_date(to_char(p_year,'fm0000')||'12-31','YYYY-MM-DD');
  LOOP
    dbms_scheduler.evaluate_calendar_string(
      calendar_string   => p_rule, 
      start_date        => start_dt,
      return_date_after => start_dt,
      next_run_date     => next_dt);
    EXIT WHEN next_dt > last_dt;
    PIPE ROW (next_dt);
    start_dt := next_dt;
  END LOOP;
END recurring_dates;
/

You'd feed the function with a calendar string in the DBMS_SCHEDULER-Syntax, and it will return the matching dates.

@rcmuthu786's last thursdays:

SELECT * FROM TABLE(recurring_dates (2013, 'FREQ=MONTHLY;BYDAY=-1 THU'));

2013-01-31
2013-02-28
2013-03-28
2013-04-25
2013-05-30
...

Or, the second Wednesdays of each month:

SELECT * FROM TABLE(recurring_dates (2013, 'FREQ=MONTHLY;BYDAY=2 WED'));
2013-01-09
2013-02-13
2013-03-13
2013-04-10
...

etc, etc...

Upvotes: 0

Related Questions