1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to get friday's in a month

Discussion in 'SQL PL/SQL' started by jayadhana, Oct 25, 2012.

  1. jayadhana

    jayadhana Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi friends,
    I want to retrieve only the friday dates of a month.

    Thx
    Dhananjaya
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi there,


    You should give more details about your question, in what form and context do you want to retrieve these date values? I mean within a SQL query run for example directly in SQL*Plus or it can be put in a PL/SQL block? How do you specify the month? by month name or by its number? in the current year?

    Please give us your exact test case.

    Regards,
    Dariyoosh
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Dhananjaya,

    Here is the query to get required day based on our input.

    Code (Text):

    SQL>  select TO_CHAR(sysdate-to_char(sysdate,'dd')+level,'DD-Mon-YYYY')"DATE" , to_char(sysdate-to_char(sysdate,'dd')+level,'fmDay')"DAY"
             from dual
             where to_char(sysdate-to_char(sysdate,'dd')+level,'fmDAY')=upper('&DAY')
             connect by level<=to_char(last_day(sysdate),'dd');

    while running it asks us to enter day required.
    Enter day as monday
    Then the output displays as shown below:

    Output: -

    DATE            WEEK
    01-Oct-2012 Monday
    08-Oct-2012 Monday
    15-Oct-2012 Monday
    22-Oct-2012 Monday
    29-Oct-2012 Monday

     
    If you want to get the days from any other date then change the sysdate in query to required date which was of your required month.
     
  4. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Well, if we assume that we read the date interval dynamically, there can be even a more general solution

    Code (Text):

    VARIABLE start_date VARCHAR2(25);
    VARIABLE end_date VARCHAR2(25);
    VARIABLE day_name VARCHAR2(10);

    EXECUTE :start_date := '&start_date';
    EXECUTE :end_date := '&end_date';
    EXECUTE :day_name := '&day_name';

    SELECT  dates,
            TO_CHAR(dates, 'DAY-MON-YYYY')
    FROM
    (
        SELECT  TO_DATE(:start_date,'DD-MON-YYYY') + ROWNUM -1 AS dates
        FROM all_objects
        WHERE ROWNUM <= TO_DATE(:end_date,'DD-MON-YYYY') -
                        TO_DATE(:start_date,'DD-MON-YYYY') + 1
    )
    WHERE UPPER(REGEXP_SUBSTR(TO_CHAR(dates, 'DAY-MON-YYYY'), '([[:alpha:]])+')) = UPPER(:day_name);
     

    In the case of the OP we're looking for the fridays, let's say for the OCTOBER 2012 so

    Code (Text):

    Enter value for start_date: 01-OCT-2012

    PL/SQL procedure successfully completed.

    Enter value for end_date: 31-OCT-2012

    PL/SQL procedure successfully completed.

    Enter value for day_name: friday

    PL/SQL procedure successfully completed.


    DATES     TO_CHAR(DATES,'DAY
    --------- ------------------
    05-OCT-12 FRIDAY   -OCT-2012
    12-OCT-12 FRIDAY   -OCT-2012
    19-OCT-12 FRIDAY   -OCT-2012
    26-OCT-12 FRIDAY   -OCT-2012

    SQL>

     


    But also, with this solution you can go even search for several months

    Code (Text):

    Enter value for start_date: 01-SEP-2012

    PL/SQL procedure successfully completed.

    Enter value for end_date: 01-DEC-2012

    PL/SQL procedure successfully completed.

    Enter value for day_name: monday

    PL/SQL procedure successfully completed.


    DATES     TO_CHAR(DATES,'DAY
    --------- ------------------
    03-SEP-12 MONDAY   -SEP-2012
    10-SEP-12 MONDAY   -SEP-2012
    17-SEP-12 MONDAY   -SEP-2012
    24-SEP-12 MONDAY   -SEP-2012
    01-OCT-12 MONDAY   -OCT-2012
    08-OCT-12 MONDAY   -OCT-2012
    15-OCT-12 MONDAY   -OCT-2012
    22-OCT-12 MONDAY   -OCT-2012
    29-OCT-12 MONDAY   -OCT-2012
    05-NOV-12 MONDAY   -NOV-2012
    12-NOV-12 MONDAY   -NOV-2012
    19-NOV-12 MONDAY   -NOV-2012
    26-NOV-12 MONDAY   -NOV-2012

    13 rows selected.

    SQL>

     

    Regards,
    Dariyoosh
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The only issue I have with this last solution is the error in the to_char format string -- it should be 'DD-MON-YYYY' instead of 'DAY-MON-YYYY' as to_char/to_date do not understand DAY in that context. A corrected script is shown below:

    Code (SQL):
    SQL> EXECUTE :start_date := '&start_date';
    Enter VALUE FOR start_date: 01-sep-2012
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXECUTE :end_date := '&end_date';
    Enter VALUE FOR end_date: 01-dec-2012
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXECUTE :day_name := '&day_name';
    Enter VALUE FOR day_name: friday
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT     dates,
      2          TO_CHAR(dates, 'DD-MON-YYYY')
      3  FROM
      4  (
      5      SELECT  TO_DATE(:start_date,'DD-MON-YYYY') + ROWNUM -1 AS dates
      6      FROM all_objects
      7      WHERE ROWNUM <= TO_DATE(:end_date,'DD-MON-YYYY') -
      8                      TO_DATE(:start_date,'DD-MON-YYYY') + 1
      9  )
     10  WHERE UPPER(REGEXP_SUBSTR(TO_CHAR(dates, 'DAY-MON-YYYY'), '([[:alpha:]])+')) = UPPER(:day_name);
     
    DATES     TO_CHAR(DATES,'DD-MO
    --------- --------------------
    07-SEP-12 07-SEP-2012
    14-SEP-12 14-SEP-2012
    21-SEP-12 21-SEP-2012
    28-SEP-12 28-SEP-2012
    05-OCT-12 05-OCT-2012
    12-OCT-12 12-OCT-2012
    19-OCT-12 19-OCT-2012
    26-OCT-12 26-OCT-2012
    02-NOV-12 02-NOV-2012
    09-NOV-12 09-NOV-2012
    16-NOV-12 16-NOV-2012
    23-NOV-12 23-NOV-2012
    30-NOV-12 30-NOV-2012
     
    13 rows selected.
     
    SQL>
    This now creates the intended output -- notice the date of the month is missing in the output from the earlier post.
     
  6. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    I don't understand what do you mean about the error of format in TO_CHAR function. What I see in the oracle documentation here:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#CDEHIFJA

    is that 'DD' in the date format of TO_CHAR gives the day number and 'DAY' gives the name of the day.

    Code (Text):

    SQL>
    SQL> SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;

    TO
    --
    25

    SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

    TO_CHAR(S
    ---------
    THURSDAY

    SQL>

     

    Besides, what I see in your output, you get two exactly identical columns. whereas my purpose was to print the
    date value as DD-MON-YYYY in the first column, and 'DAY-MON-YYYY' (to have the name of the day instead of
    number). So one column gives the date as we have usually (for example 01-JAN-2012) and the other one
    (just for clarity) prints the day name in order to show that everything that is printed is the date of the desired
    day of the month (for example Friday)

    I just run for the second time the query that I posted this morning, so I don't really understand what error you got
    when you run my script.


    Regards,
    Dariyoosh
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I misread the output from your query -- my apologies for the mixup.

    I do want to say your solution is a very clean and impressive offering -- well done.

    Again, my apologies for misreading the output.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    After looking at the query yet again your intent with the date text format is good but it doesn't make much sense to me given the embedded '-' characters (that may have led to my misreading the actual output as I saw 'missing' date parts). I would like to offer this modification:

    Code (SQL):
    SQL> EXECUTE :start_date := '&start_date';
    Enter VALUE FOR start_date: 01-sep-2012
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXECUTE :end_date := '&end_date';
    Enter VALUE FOR end_date: 01-dec-2012
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXECUTE :day_name := '&day_name';
    Enter VALUE FOR day_name: friday
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT     dates,
      2          TO_CHAR(dates, 'DAY MONTH YYYY')
      3  FROM
      4  (
      5      SELECT  TO_DATE(:start_date,'DD-MON-YYYY') + ROWNUM -1 AS dates
      6      FROM all_objects
      7      WHERE ROWNUM <= TO_DATE(:end_date,'DD-MON-YYYY') -
      8                      TO_DATE(:start_date,'DD-MON-YYYY') + 1
      9  )
     10  WHERE UPPER(REGEXP_SUBSTR(TO_CHAR(dates, 'DAY-MON-YYYY'), '([[:alpha:]])+')) = UPPER(:day_name);
     
    DATES     TO_CHAR(DATES,'DAYMONTHYYYY')
    --------- ------------------------------------------------------------------------------
    07-SEP-12 FRIDAY    SEPTEMBER 2012
    14-SEP-12 FRIDAY    SEPTEMBER 2012
    21-SEP-12 FRIDAY    SEPTEMBER 2012
    28-SEP-12 FRIDAY    SEPTEMBER 2012
    05-OCT-12 FRIDAY    OCTOBER   2012
    12-OCT-12 FRIDAY    OCTOBER   2012
    19-OCT-12 FRIDAY    OCTOBER   2012
    26-OCT-12 FRIDAY    OCTOBER   2012
    02-NOV-12 FRIDAY    NOVEMBER  2012
    09-NOV-12 FRIDAY    NOVEMBER  2012
    16-NOV-12 FRIDAY    NOVEMBER  2012
    23-NOV-12 FRIDAY    NOVEMBER  2012
    30-NOV-12 FRIDAY    NOVEMBER  2012
     
    13 ROWS selected.
     
    SQL>
    To me it better reports the day of the week , the month and the year.

    Of course this is simply a suggestion.
     
  9. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Perfect! This is in fact much better.

    Yes, writing on several lines FRIDAY-NOVEMBER-2012 doesn't make make much sense.
    But writing separately (as you suggest) FRIDAY NOVEMBER 2012 illustrates better what
    is the purpose and what we want to show.

    Thanks for this suggestion.


    Regards,
    Dariyoosh
    :cool:
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is another query without using Regular expression.

    Code (SQL):
    SQL> WITH yr AS ( SELECT 2012 yy
      2               FROM dual ),
      3  dt AS (SELECT to_date( yy||01, 'YYYYMM')-1 + level dd
           FROM yr
      4    5         CONNECT BY level <= to_date( yy+1||01, 'YYYYMM') - to_date( yy||01, 'YYYYMM'))
      6  SELECT dd Friday_date , To_char(dd,'fmDAY MONTH YYYY') date_desc
      7  FROM dt
      8  WHERE to_char(dd,'D') ='6' -- day of week 6 for Firday
      9  ;

    FRIDAY_DA DATE_DESC
    --------- ------------------------
    06-JAN-12 FRIDAY JANUARY 2012
    13-JAN-12 FRIDAY JANUARY 2012
    20-JAN-12 FRIDAY JANUARY 2012
    27-JAN-12 FRIDAY JANUARY 2012
    03-FEB-12 FRIDAY FEBRUARY 2012
    10-FEB-12 FRIDAY FEBRUARY 2012
    17-FEB-12 FRIDAY FEBRUARY 2012
    24-FEB-12 FRIDAY FEBRUARY 2012
    02-MAR-12 FRIDAY MARCH 2012
    09-MAR-12 FRIDAY MARCH 2012
    16-MAR-12 FRIDAY MARCH 2012
    23-MAR-12 FRIDAY MARCH 2012
    30-MAR-12 FRIDAY MARCH 2012
    06-APR-12 FRIDAY APRIL 2012
    13-APR-12 FRIDAY APRIL 2012
    20-APR-12 FRIDAY APRIL 2012
    27-APR-12 FRIDAY APRIL 2012
    04-MAY-12 FRIDAY MAY 2012
    11-MAY-12 FRIDAY MAY 2012
    18-MAY-12 FRIDAY MAY 2012
    25-MAY-12 FRIDAY MAY 2012
    01-JUN-12 FRIDAY JUNE 2012
    08-JUN-12 FRIDAY JUNE 2012
    15-JUN-12 FRIDAY JUNE 2012
    22-JUN-12 FRIDAY JUNE 2012
    29-JUN-12 FRIDAY JUNE 2012
    06-JUL-12 FRIDAY JULY 2012
    13-JUL-12 FRIDAY JULY 2012
    20-JUL-12 FRIDAY JULY 2012
    27-JUL-12 FRIDAY JULY 2012
    03-AUG-12 FRIDAY AUGUST 2012
    10-AUG-12 FRIDAY AUGUST 2012
    17-AUG-12 FRIDAY AUGUST 2012
    24-AUG-12 FRIDAY AUGUST 2012
    31-AUG-12 FRIDAY AUGUST 2012
    07-SEP-12 FRIDAY SEPTEMBER 2012
    14-SEP-12 FRIDAY SEPTEMBER 2012
    21-SEP-12 FRIDAY SEPTEMBER 2012
    28-SEP-12 FRIDAY SEPTEMBER 2012
    05-OCT-12 FRIDAY OCTOBER 2012
    12-OCT-12 FRIDAY OCTOBER 2012
    19-OCT-12 FRIDAY OCTOBER 2012
    26-OCT-12 FRIDAY OCTOBER 2012
    02-NOV-12 FRIDAY NOVEMBER 2012
    09-NOV-12 FRIDAY NOVEMBER 2012
    16-NOV-12 FRIDAY NOVEMBER 2012
    23-NOV-12 FRIDAY NOVEMBER 2012
    30-NOV-12 FRIDAY NOVEMBER 2012
    07-DEC-12 FRIDAY DECEMBER 2012
    14-DEC-12 FRIDAY DECEMBER 2012
    21-DEC-12 FRIDAY DECEMBER 2012
    28-DEC-12 FRIDAY DECEMBER 2012

    52 ROWS selected.

    SQL>