14 Replies Latest reply: Feb 26, 2013 1:36 AM by carmac RSS

    Oracle date function help

    carmac
      Hi all,

      I need to write a function below scenario
      I need to pass two parameters from_date and to_date
      i want to get all dates between from_date and to_date except Saturdays and include from_date and to_date also.

      Please help me.

      Thanks,

      Edited by: carmac on Jan 29, 2013 5:04 PM
        • 1. Re: Oracle date function help
          Purvesh K
          create or replace procedure get_dates(p_from_date date, p_to_date date)
          is
          
          begin
            for i in ( select date_column from your_table where date_column between p_from_date and p_to_date and trim(to_char(date_column, 'day')) != 'SATURDAY' )
            loop
              dbms_output.put_line('Date :: ' || i);
            end loop;
          end;
          • 2. Re: Oracle date function help
            Manik
            Check in plain SQL.. Enter the dates inthe format YYYYMMDD
            WITH t AS
                    (SELECT TO_DATE (:dt1, 'YYYYMMDD') startdt,
                            TO_DATE (:dt2, 'YYYYMMDD') enddt
                       FROM DUAL)
            SELECT *
              FROM (    SELECT DECODE (TO_CHAR (startdt + ROWNUM, 'DY'),
                                       'SAT', NULL,
                                       startdt + ROWNUM)
                                  dt
                          FROM t
                    CONNECT BY ROWNUM <= (enddt - startdt))
             WHERE dt IS NOT NULL;
            PL/SQL approach (Function):---
            CREATE TYPE t_dt AS OBJECT
                   (dt DATE);
            
            
            CREATE TYPE t_dt_tbl IS TABLE OF t_dt;
            
            
            CREATE OR REPLACE FUNCTION fn_ret_dts_no_sat (p_dt1 varchar2, p_dt2 varchar2)
               RETURN t_dt_tbl
               PIPELINED AS
            BEGIN
               FOR rec
                  IN (WITH t AS
                              (SELECT TO_DATE (p_dt1, 'YYYYMMDD') startdt,
                                      TO_DATE (p_dt2, 'YYYYMMDD') enddt
                                 FROM DUAL)
                      SELECT *
                        FROM (    SELECT DECODE (TO_CHAR (startdt + ROWNUM, 'DY'),
                                                 'SAT', NULL,
                                                 startdt + ROWNUM)
                                            dt
                                    FROM t
                              CONNECT BY ROWNUM <= (enddt - startdt))
                       WHERE dt IS NOT NULL) LOOP
                  PIPE ROW (t_dt (rec.dt));
               END LOOP;
            
               RETURN;
            END;
            /
            Testing:
            select * from table(fn_ret_dts_no_sat('20120101','20120131'));
            Output:
            DT
            ------
            1/2/2012
            1/3/2012
            1/4/2012
            1/5/2012
            1/6/2012
            1/8/2012
            1/9/2012
            1/10/2012
            1/11/2012
            1/12/2012
            1/13/2012
            1/15/2012
            1/16/2012
            1/17/2012
            1/18/2012
            1/19/2012
            1/20/2012
            1/22/2012
            1/23/2012
            1/24/2012
            1/25/2012
            1/26/2012
            1/27/2012
            1/29/2012
            1/30/2012
            1/31/2012
            Cheers,
            Manik.

            Edited by: Included Function approach.
            • 3. Re: Oracle date function help
              chris227
              Something like this, advantage, NLS-independent
              declare
              procedure get_diff (p_date1 date, p_date2 date)
              as
              begin
              DBMS_OUTPUT.PUT_LINE(
              'From '
              ||to_char(p_date1,'YYYY-MM-DD')
              ||' to '
              ||to_char(p_date2,'YYYY-MM-DD')
              );
               for rec in (select p_date1 + level-1 d, to_char(p_date1 + level-1,'DAY-MON-YYYY') c
                           from dual
                           where
                           mod(to_number(to_char(p_date1 +level - 1 ,'J')),7) != 5
                           connect by
                           level <= p_date2 - p_date1 + 1
                           )
               loop
                  DBMS_OUTPUT.PUT_LINE(rec.c||' : '||to_char(rec.d,'YYYY-MM-DD'));
               end loop;
              end;
              begin
               get_diff(sysdate-10, sysdate);
              end;
              
              From 2013-02-15 to 2013-02-25
              FRIDAY   -FEB-2013 : 2013-02-15
              SUNDAY   -FEB-2013 : 2013-02-17
              MONDAY   -FEB-2013 : 2013-02-18
              TUESDAY  -FEB-2013 : 2013-02-19
              WEDNESDAY-FEB-2013 : 2013-02-20
              THURSDAY -FEB-2013 : 2013-02-21
              FRIDAY   -FEB-2013 : 2013-02-22
              SUNDAY   -FEB-2013 : 2013-02-24
              MONDAY   -FEB-2013 : 2013-02-25
              Edited by: chris227 on 25.01.2013 00:42

              Edited by: chris227 on 25.02.2013 03:43
              correction
              • 4. Re: Oracle date function help
                carmac
                i want to get all dates between from_date and to_date except Saturdays and include from_date and to_date also
                • 5. Re: Oracle date function help
                  Frank Kulash
                  Hi,
                  carmac wrote:
                  i want to get all dates between from_date and to_date except Saturdays and include from_date and to_date also
                  You can change Manik's solution like this:
                  WITH t AS
                          (SELECT TO_DATE (:dt1, 'YYYYMMDD') startdt,
                                  TO_DATE (:dt2, 'YYYYMMDD') enddt
                             FROM DUAL)
                  SELECT *
                    FROM (    SELECT DECODE ( TO_CHAR (startdt + ROWNUM - 1     -- ***  CHANGED  ***
                                                 , 'DY'),
                                             'SAT', NULL,
                                             startdt + ROWNUM - 1               -- ***  CHANGED  ***
                                   )
                                        dt
                                FROM t
                          CONNECT BY ROWNUM <= (enddt + 1                    -- ***  CHANGED  ***
                                                  - startdt))
                   WHERE dt IS NOT NULL;
                  • 6. Re: Oracle date function help
                    Purvesh K
                    carmac wrote:
                    i want to get all dates between from_date and to_date except Saturdays and include from_date and to_date also
                    It would have been great if you could talk in terms of SQL, Point out amongst 3 posted solutions, what is wrong and what you expect.

                    Since, you are ready to do neither of it, there isn't much that I consider myself of much help.
                    • 7. Re: Oracle date function help
                      carmac
                      Further Clarification
                      • 8. Re: Oracle date function help
                        carmac
                        Hi Manik,

                        Your code is very helpful to me but i have no permission to create CREATE TYPE.

                        I want to create a package and include these types in package specification.

                        how to use these types in package body.

                        Please help me.

                        Thanks
                        • 9. Re: Oracle date function help
                          Manik
                          Make USE OF REF cursors here.

                          CHECK this :
                          CREATE OR REPLACE PACKAGE pkg_dts_test AS
                             PROCEDURE pr_ret_dts_no_sat (p_dt1            VARCHAR2,
                                                          p_dt2            VARCHAR2,
                                                          p_out_recs   OUT SYS_REFCURSOR);
                          END;
                          /
                          
                          
                          
                          CREATE OR REPLACE PACKAGE BODY pkg_dts_test AS
                             PROCEDURE pr_ret_dts_no_sat (p_dt1            VARCHAR2,
                                                          p_dt2            VARCHAR2,
                                                          p_out_recs   OUT SYS_REFCURSOR) AS
                             BEGIN
                                OPEN p_out_recs FOR
                                   WITH t AS
                                           (SELECT TO_DATE (p_dt1, 'YYYYMMDD') startdt,
                                                   TO_DATE (p_dt2, 'YYYYMMDD') enddt
                                              FROM DUAL)
                                   SELECT *
                                     FROM (    SELECT DECODE (TO_CHAR (startdt + ROWNUM - 1, 'DY'),
                                                              'SAT', NULL,
                                                              startdt + ROWNUM - 1)
                                                         dt
                                                 FROM t
                                           CONNECT BY ROWNUM <= (enddt + 1 - startdt))
                                    WHERE dt IS NOT NULL;
                             END;
                          END;
                          /
                          TO TEST this you can USE :
                          SET SERVEROUTPUT ON SIZE 1000000
                          
                          DECLARE
                             l_cursor   SYS_REFCURSOR;
                             l_var      DATE;
                          BEGIN
                             pkg_dts_test.pr_ret_dts_no_sat (
                                p_dt1        => TO_CHAR (SYSDATE - 100, 'YYYYMMDD'),
                                p_dt2        => TO_CHAR (SYSDATE, 'YYYYMMDD'),
                                p_out_recs   => l_cursor);
                          
                             LOOP
                                FETCH l_cursor INTO l_var;
                          
                                EXIT WHEN l_cursor%NOTFOUND;
                                DBMS_OUTPUT.PUT_LINE (l_var);
                             END LOOP;
                          
                             CLOSE l_cursor;
                          END;
                          /
                          Cheers,
                          Manik.
                          • 10. Re: Oracle date function help
                            carmac
                            Return date should be in YYYYMMDD format
                            • 11. Re: Oracle date function help
                              Manik
                              You may try this
                              SET SERVEROUTPUT ON SIZE 1000000
                               
                              DECLARE
                                 l_cursor   SYS_REFCURSOR;
                                 l_var      date;
                              BEGIN
                                 pkg_dts_test.pr_ret_dts_no_sat (
                                    p_dt1        => TO_CHAR (SYSDATE - 100, 'YYYYMMDD'),
                                    p_dt2        => TO_CHAR (SYSDATE, 'YYYYMMDD'),
                                    p_out_recs   => l_cursor);
                               
                                 LOOP
                                    FETCH l_cursor INTO l_var;
                               
                                    EXIT WHEN l_cursor%NOTFOUND;
                                    DBMS_OUTPUT.PUT_LINE (to_char(l_var,'YYYYMMDD'));  -------------------------> observe added to_char
                                 END LOOP;
                               
                                 CLOSE l_cursor;
                              END;
                              / 
                              Cheers,
                              Manik.
                              • 12. Re: Oracle date function help
                                carmac
                                Thank you Manik
                                • 13. Re: Oracle date function help
                                  carmac
                                  further clarification
                                  • 14. Re: Oracle date function help
                                    carmac
                                    Thank u...

                                    Edited by: carmac on Feb 26, 2013 1:05 PM