4 Replies Latest reply: Nov 28, 2013 5:37 PM by lilhelp RSS

    How to subtract the month from an item type Date Picker

    Lecc23

      Hi everybody!!

       

      I´m building an application (V4.2) in wich customer introduce a date (item P11_F_ACTIVIDAD, Display as Date Picker). I need to sustract the month from that field and use it in a SQL query to fetch all rows from a table that met that condition (column F_ACTIVIDAD) . The WHERE clause should has something like this: WHERE substr(to_char(h.F_ACTIVIDAD,'ddmmyyyy'),3,2)= substr(to_char(:P11_F_ACTIVIDAD,'ddmmyyyy'),3,2)  but SQL engine does not see ":P11_F_ACTIVIDAD" like a column name..... Of course, it is not a column name.

       

      I appreciate recomend me an idea to overcome this issue.

       

      Thanks so much!

      Luis

        • 1. Re: How to subtract the month from an item type Date Picker
          jwellsnh

          Lecc23 wrote:

           

          Hi everybody!!

           

          I´m building an application (V4.2) in wich customer introduce a date (item P11_F_ACTIVIDAD, Display as Date Picker). I need to sustract the month from that field and use it in a SQL query to fetch all rows from a table that met that condition (column F_ACTIVIDAD) . The WHERE clause should has something like this: WHERE substr(to_char(h.F_ACTIVIDAD,'ddmmyyyy'),3,2)= substr(to_char(:P11_F_ACTIVIDAD,'ddmmyyyy'),3,2)  but SQL engine does not see ":P11_F_ACTIVIDAD" like a column name..... Of course, it is not a column name.

           

          I appreciate recomend me an idea to overcome this issue.

           

          Thanks so much!

          Luis

          In APEX, items can certainly be used as bound variables in a SQL statement; like this.

          SELECT emp.ename,
                 emp.hiredate,
                 EXTRACT (MONTH FROM emp.hiredate) hiredate_month,
                 EXTRACT (MONTH FROM :p11_f_actividad) datepicker_month
            FROM emp
           WHERE SUBSTR (TO_CHAR (emp.hiredate, 'ddmmyyyy'), 3, 2) =
                    SUBSTR (TO_CHAR ( :p11_f_actividad, 'ddmmyyyy'), 3, 2)
          
          

           

          Jeff

          • 2. Re: How to subtract the month from an item type Date Picker
            Mike Kutz

            The :P11_F_ACTIVIDAD are treated as VARCHAR2 data types.

            EMP.HIREDATE and H.ACTIVIDAD are DATE data types.

             

            You should be comparing dates to dates.

            You should never compare strings-that-look-like-dates to other-strings-that-look-like-dates and believe that you'll get the right answer.

             

            If you want all the rows that are in the same month and year as the date picker

            WHERE
              trunc( H.ACTIVEDAD, 'MON' ) -- truncates the date to 1st day of the month and keeps it as a DATE
            = trunc(
                  to_date( :P11_F_ACTIVIDAD, 'DDMMYYYY') -- always explicitly convert strings to dates
                ,'MON' )
            

             

            The trunc() on the column will force a full-table-scan (ie slow=true) unless you have a function based index on that column.

             

            MK

            • 3. Re: How to subtract the month from an item type Date Picker
              Lecc23

              Thanks Jef!!!..I appreciate it!

              • 4. Re: How to subtract the month from an item type Date Picker
                lilhelp

                Hi there,

                try the add_months function

                add_months(:P38_EPSB_AD_DATE, -1) should give you the date of a month ago

                 

                Easy illustration is to try this

                 

                 

                select add_months(sysdate,-1) from dual