1 Reply Latest reply on Nov 7, 2013 7:19 PM by Mike Kutz

    Date picker format


      Hi all,

           I have an apex page where 2 date pickers are there (ie) from_date  and   to_date

      After selecting these dates and if submit button is pressed, a report is shown which is fetched via a query.


      Report query :

      select * from (select table_name,to_char(to_date(STATS_FROM_TIMESTAMP, 'yyyy-mm-dd:hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') "STATS_FROM_TIMESTAMP"

      from table_history where to_char(to_date(STATS_FROM_TIMESTAMP, 'yyyy-mm-dd:hh24:mi:ss')) >= :P11_X1

      and to_char(to_date(STATS_FROM_TIMESTAMP, 'yyyy-mm-dd:hh24:mi:ss')) <= :P11_X2


      Here :P11_X1 and :P11_X2 are datepicker items.


      Condition for the report query to display is :

      select 1 from dual where :P11_X1 >= (select sysdate-8 from dual) and :P11_X2 <= (select sysdate from dual) and :P11_X2 >= :P11_X1 and :P11_X3 is not null


      Now my issue is,

      if suppose I give 30th october in :P11_X1 and 1st november in :P11_X2 its showing an error of

      "from_date should be greater than to_date"



      How to resolve this issue? I tried out many a things, but still I did not get the correct thing.


      Pls help me out.


      Thanks in advance

        • 1. Re: Date picker format
          Mike Kutz

          Did you get an ORA- number with that error???

          Does it show up in the Validation Failed (i forgot the appropriate name) location?

          Or did it show up in the Reports region??


          required work

          From a Schema design standpoint, make sure that the STATS_FROM_TIMESTAMP column is either a DATE data type or a TIMESTAMP data type.

          If it is not, you really need to get that fixed before you proceed.


          coding tip:

          you don't need the 'scalar SELECT'.  personally, i think they look ugly and can be hard to read


          (select sysdate from dual)

          to just



          coding tip: ** this may be your problem **

          I believe (even with Date Picker) that all of the BIND variable (P11_X2, etc) are actually VARCHAR2 data.

          As such, you need to have the TO_DATE() function surrounding them otherwise you'll find out that 2013-NOV-blah comes before 2013-OCT-blah much in the same way that dictionaries have the word NOVEMBER before the word OCTOBER.

          (ie you are comparing STRINGS... not DATES)


          enhancement suggestion

          It looks like your CONDITION type is an EXISTS

          Change your CONDITION type to be a PL/SQL FUNCTION RETURNING A BOOLEAN


          Example would look like:

            L_var_1 date;
            L_var_2 date;
            -- convert the input STRINGS/VARCHAR2 into DATE data types
            L_var_1 := to_date( P11_X1, '{the date format for your picker}' );
            L_var_2 := to_date( P11_X1, '{the date format for your picker}' );
            -- feel free to add APEX_DEBUG code here
            -- test for the bad conditions and return FALSE
            if L_var_2 > L_var_1 -- was P11_X2 <= P11_X1
              return false;
            elsif P11_X3 is null  -- P11_X3 is not null
              return false;
            elsif L_var_1 < sysdate - 8 -- was P11_X1 >= sysdate - 8
              return false
            elsif L_var_2 > sysdate -- this may need to be TRUNC( sysdate )
              return false
            end if;
            -- all tests passed.
            return true;