Need WHERE clause to filter last day in month; and second Friday in month
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need WHERE clause to filter last day in month; and second Friday in month

Hybrid View

  1. #1
    Join Date
    Aug 2017
    Posts
    1

    Need WHERE clause to filter last day in month; and second Friday in month

    Assume I have a table "mytable" with (among others) a DATE column labelled "mydate".

    Now I am searching for WHERE clauses for the following filters:

    1.) How do I find all rows where the date value represents the last day in month?

    2.) How do I find all rows where the date value is the second friday in month?

    3.) How do I find all rows where the date value is the 10th of a month?

    Thank you
    Peter

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,157
    This should give you what you want. You can change 13 to a larger number if you want additional months.

    Code:
    SQL> SELECT month_num, next_date
      2    FROM ( WITH next_12_months AS (
      3         SELECT rownum month_num
      4           FROM dba_objects
      5          WHERE ROWNUM < 13 )
      6         SELECT month_num, ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)-1 next_date
      7           FROM next_12_months
      8          UNION ALL
      9         SELECT month_num, NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), (month_num-1))+6, 'FRIDAY') next_date
     10           FROM next_12_months
     11          WHERE NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)+6, 'FRIDAY') >= TRUNC(SYSDATE)
     12          UNION ALL
     13         SELECT month_num, ADD_MONTHS(TRUNC(SYSDATE, 'MM'), (month_num-1))+9 next_date
     14           FROM next_12_months
     15          WHERE ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)+9 >= TRUNC(SYSDATE) )
     16   ORDER BY month_num, next_date;
    
     MONTH_NUM NEXT_DATE
    ---------- ---------------
             1 10-AUG-17
             1 11-AUG-17
             1 31-AUG-17
             2 08-SEP-17
             2 10-SEP-17
             2 30-SEP-17
             3 10-OCT-17
             3 13-OCT-17
             3 31-OCT-17
             4 10-NOV-17
             4 10-NOV-17
             4 30-NOV-17
             5 08-DEC-17
             5 10-DEC-17
             5 31-DEC-17
             6 10-JAN-18
             6 12-JAN-18
             6 31-JAN-18
             7 09-FEB-18
             7 10-FEB-18
             7 28-FEB-18
             8 09-MAR-18
             8 10-MAR-18
             8 31-MAR-18
             9 10-APR-18
             9 13-APR-18
             9 30-APR-18
            10 10-MAY-18
            10 11-MAY-18
            10 31-MAY-18
            11 08-JUN-18
            11 10-JUN-18
            11 30-JUN-18
            12 10-JUL-18
            12 13-JUL-18
            12 31-JUL-18
    
    36 rows selected.
    
    Elapsed: 00:00:00.19
    this space intentionally left blank

Posting Permissions

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


Click Here to Expand Forum to Full Width