Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: to disply 4th friday

    give me query for 4th Friday date in every month in a year?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one way, with a little help of analytical functions. When reviewing what it does and how, execute query by query.
    Code:
    SQL> with datum as
      2    (select trunc(sysdate, 'yyyy') + level - 1 dat
      3     from dual
      4     connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) -
      5                         trunc(sysdate, 'yyyy') - 1
      6    ),
      7    friday as
      8    (select
      9       dat,
     10       case when trim(to_char(dat, 'day')) = 'friday' then 1
     11            else 0
     12       end fri
     13     from datum
     14    ),
     15    red as
     16    (select
     17       dat,
     18       rank() over (partition by to_char(dat, 'mm'), fri order by dat) rb
     19     from friday
     20    )
     21  select dat
     22  from red
     23  where trim(to_char(dat, 'day')) = 'friday'
     24    and rb = 4
     25  order by dat;
    
    DAT
    ----------
    28.01.2011
    25.02.2011
    25.03.2011
    22.04.2011
    27.05.2011
    24.06.2011
    22.07.2011
    26.08.2011
    23.09.2011
    28.10.2011
    25.11.2011
    23.12.2011
    
    12 rows selected.
    
    SQL>

  3. #3
    Join Date
    Sep 2011
    Posts
    6
    actually i didnt get ur answer.. let me try with ur reply..

    if possible make it easy way..

    anyway thank you for reply & response..

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    An easy way is to take a calendar off the wall, turn its pages and mark 4th Friday in every month with a red felt pen.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >if possible make it easy way..
    Easy enough?

    Code:
      1  with
      2	 data as (
      3	   select to_date('01/01/2011', 'DD/MM/YYYY') date1,
      4		  to_date('31/12/2011', 'DD/MM/YYYY') date2
      5	   from dual
      6	 )
      7    select to_char(date1+level-1, 'DD/MM/YYYY') the_date
      8    from data
      9    where to_char(date1+level-1,'W') = '4'
     10	and  to_char(date1+level-1,'DY') = 'FRI'
     11*   connect by level <= date2-date1+1
    SQL> /
    
    THE_DATE
    ----------
    28/01/2011
    25/02/2011
    25/03/2011
    22/04/2011
    27/05/2011
    24/06/2011
    22/07/2011
    26/08/2011
    23/09/2011
    28/10/2011
    25/11/2011
    23/12/2011
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •