Finding 2nd and 4th Thursdays 4

[Welshbird]

I have specific meetings that happen on the 2nd and 4th Thursdays of each month (and non-work things that happen on the 2nd and 4th Sunday).

This isn't on the 2nd or 4th week though, so sometimes the 2nd Thursday is not on the same week as the 2nd Sunday...

I just wondered if there was a cunning way of calculating these dates for 2007 in Oracle rather than wandering through my calendar and marking them manually.

(I was hoping that if I could build a table with this info I can spool it out and then munge it into my calendar.)

I'd really appreciate any help.

Thanks

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[taupirho]

Its a bit clunky but try

select * from
(
select mm,dd,dte,row_number() over(partition by mm order by mm) x from
(
select
to_char(to_date('01-jan-07')+rownum,'mon') mm,
to_char(to_date('01-jan-07')+rownum,'dy') dd ,
to_char(to_date('01-jan-07')+rownum,'dd-mon-yy') cdte ,
to_date(to_char(to_date('01-jan-07')+rownum,'dd-mon-yy')) dte
from all_objects
where to_date('01-jan-07')+rownum <= '31-dec-07'
)
where dd ='thu'
)
where x in ( 2 ,4)
order by 3,4

 

[Dagon]

To get the second thursday, you could probably do something like:

select next_day(next_day('01-jan-07', 'Thu'), 'Thu') from dual

Some PL/SQL would enable you to loop through and get all the dates:

declare
    v_first_date DATE := '01-jan-07';
begin
    for i in 1..12 loop
	   if to_char(v_first_date, 'Dy') = 'Thu' then
	      dbms_output.put_line(next_day(v_first_date, 'Thu'));
	   else
	      dbms_output.put_line(next_day(next_day(v_first_date, 'Thu'), 'Thu'));
       end if;
	   v_first_date := add_months(v_first_date,1);
	end loop;
end;

 

[taupirho]

My original code was not quite right

change (partition by mm order by mm)
to (partition by mm order by dte)

 

[karluk]

I am borrowing a lot of the ideas from taupirho's suggestion, but the following code generates the second and fourth Thursdays of each month in 2007. You can get the second and fourth Sundays by changing all the occurrences of "Thursday" in the sql to "Sunday".

select Thursday, trunc(to_char(thursday,'dd')/7 + 1) as x from
    (select next_day('31-dec-2006','Thursday')+7*rownum as Thursday
     from all_objects where rownum < 52)
where trunc(to_char(Thursday,'dd')/7 + 1) in (2,4)

 

[Dagon]

As a side issue, if you want a row generator there is a much better way of doing it than using all_objects. You can use the following:

select * from dual connect by level < 52

 

[Welshbird]

Thanks guys. That's exactly what I wanted.

Stars all round...

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Except... If I change all Thursdays to Sunday, it then tells me that 21st Jan is the 4th Sunday, when it isn't... I'll play around with that though and see if I can't work out why.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[taupirho]

Not on my system it doesn't

1 select * from
2 (
3 select mm,dd,dte,row_number() over(partition by mm order by dte) x f
4 (
5 select
6 to_char(to_date('01-jan-07')+rownum,'mon') mm,
7 to_char(to_date('01-jan-07')+rownum,'dy') dd ,
8 to_char(to_date('01-jan-07')+rownum,'dd-mon-yy') cdte ,
9 to_date(to_char(to_date('01-jan-07')+rownum,'dd-mon-yy')) dte
10 from all_objects
11 where to_date('01-jan-07')+rownum <= '31-dec-07'
12 )
13 where dd ='sun'
14 )
15 where x in ( 2 ,4)
16* order by 3,4
SQL> /

MM DD DTE X
--- --- --------- ----------
jan sun 14-JAN-07 2
jan sun 28-JAN-07 4
feb sun 11-FEB-07 2
feb sun 25-FEB-07 4
mar sun 11-MAR-07 2
mar sun 25-MAR-07 4
apr sun 08-APR-07 2
apr sun 22-APR-07 4
may sun 13-MAY-07 2
may sun 27-MAY-07 4
jun sun 10-JUN-07 2

MM DD DTE X
--- --- --------- ----------
jun sun 24-JUN-07 4
jul sun 08-JUL-07 2
jul sun 22-JUL-07 4
aug sun 12-AUG-07 2
aug sun 26-AUG-07 4
sep sun 09-SEP-07 2
sep sun 23-SEP-07 4
oct sun 14-OCT-07 2
oct sun 28-OCT-07 4
nov sun 11-NOV-07 2
nov sun 25-NOV-07 4

MM DD DTE X
--- --- --------- ----------
dec sun 09-DEC-07 2
dec sun 23-DEC-07 4

24 rows selected.

 

[Welshbird]

Excellent! Thank you.

(I made a wee change cos the 'from' had got truncated to an 'f')

select * from
(
select mm,dd,dte,row_number() over(partition by mm order by dte) x from
(
select
to_char(to_date('01-jan-07')+rownum,'mon') mm,
to_char(to_date('01-jan-07')+rownum,'dy') dd ,
to_char(to_date('01-jan-07')+rownum,'dd-mon-yy') cdte ,
to_date(to_char(to_date('01-jan-07')+rownum,'dd-mon-yy')) dte
from all_objects
where to_date('01-jan-07')+rownum <= '31-dec-07'
)
where dd ='sun'
)
where  x in ( 2 ,4)
order by 3,4
/

Thanks again.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

My PL/SQL works correctly:

set serveroutput on
declare
    v_first_date DATE := '01-jan-07';
	v_sun_date date;
begin
    for i in 1..12 loop
       if to_char(v_first_date, 'Dy') = 'Sun' then
	      v_sun_date := v_first_date;
	      for i in 1..3 loop
		     v_sun_date := next_day(v_sun_date, 'Sun');
          end loop;
          dbms_output.put_line(v_sun_date);
       else
	      v_sun_date := v_first_date;
	      for i in 1..4 loop
		     v_sun_date := next_day(v_sun_date, 'Sun');
          end loop;
          dbms_output.put_line(v_sun_date);
       end if;
       v_first_date := add_months(v_first_date,1);
    end loop;
end;

28-JAN-07
25-FEB-07
25-MAR-07
22-APR-07
27-MAY-07
24-JUN-07
22-JUL-07
26-AUG-07
23-SEP-07
28-OCT-07
25-NOV-07
23-DEC-07

 

[Welshbird]

Um - Dagon - I don't think it does. I was looking for the 2nd AND 4th Sundays... rather than just the 4th...

Ta though.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[karluk]

Yes, willif, you are right that my suggestion isn't right. The "trunc" fails whenever the expression inside is an integer. Please excuse the sloppy coding. For 2007 it generates the wrong Sundays in January and October and the wrong Thursdays in June. The following change will work.

select Thursday, trunc((to_char(Thursday,'dd')-1)/7) + 1 as x from
    (select next_day('31-dec-2006','Thursday')+7*rownum as Thursday
     from all_objects where rownum < 52)
where trunc((to_char(Thursday,'dd')-1)/7) + 1 in (2,4)

 

[karluk]

Thank you, Dagon, for the tip. I've used "select ... from all_objects" in this situation for ages, but it's never to late to adopt an improvement. Incorporating it into the Thursday calculation, the sql becomes

select Thursday, trunc((to_char(Thursday,'dd')-1)/7) + 1 as x from
    (select next_day('31-dec-2006','Thursday')+7*rownum as Thursday
     from dual connect by level < 52)
where trunc((to_char(Thursday,'dd')-1)/7) + 1 in (2,4)

 

[Dagon]

Um - Dagon - I don't think it does. I was looking for the 2nd AND 4th Sundays... rather than just the 4th...

I was illustrating the principle rather than giving a complete solution, but it would be easy to amend it to give both 2nd and 4th.

 

[Welshbird]

Sorry - I realise that was a rather terse reply!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]