5 Replies Latest reply: Apr 25, 2013 6:40 AM by Denes Kubicek RSS

    apex report string column to date

    Irha10
      v: Apex 4.2

      Hello everyone,

      anyone can help me for following please?

      I have Dynamic report and column say some_date column and data can be in the columun (01-JAN-13) OR (01-JAN-13,04-JAN-13), (01-JAN-13,04-JAN-13,10-JAN-13) so on.

      in actual table column some_date is date field but in report as above (string) because using RTRIM(some_date, ',') some_date within the select MODEL clause.

      my problem is when run the report and select filter from action menu then it doesn't give option such as < , > or so on because of string but I want it should give <, <=, >, >= operators too as date column. how and what should I do please?

      Many thanks.
        • 1. Re: apex report string column to date
          Denes Kubicek
          If the column is of type date, why do you need to use RTRIM? Because the column is not a date but a varchar2 column. Try using TO_DATE (RTRIM (date_column, ','), 'DD-MON-YY') instead.

          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------
          • 2. Re: apex report string column to date
            Irha10
            thx for reply. It doesn't work which you said. reason using rtrim if multiple row in the table with same ID so displaying in onw row e.g

            101 abc 01-jan-2013
            101 abc 10-jan-2013

            so using model stuff and showing as 101 abc 01-jan-2013,10-jan-2013 in one row. so date column become string from date and can't give option < , > for filter.

            regards.
            • 3. Re: apex report string column to date
              Denes Kubicek
              I am not sure what you mean by:

              " It doesn't work..."

              Do you have multiple column or all of your data is only one column? :)

              Denes Kubicek
              -------------------------------------------------------------------
              http://deneskubicek.blogspot.com/
              http://www.apress.com/9781430235125
              http://apex.oracle.com/pls/apex/f?p=31517:1
              http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
              -------------------------------------------------------------------
              • 4. Re: apex report string column to date
                Irha10
                if I have multiple rows on same ID as mentioned earlier so in report showing only one row as follows:

                ID Description Date
                101, abc 01-Jan-2013
                101, abc 15-jan-2013

                so in report 101 abc (01-Jan-2013,15-Jan-2013) data will be showing in one column seprated by comma. so when user try to filter on date colum using report then unable to select <, > operators because of data type is string but in actaul table is data type is Date. I understand it is difficult but thinking if any possibility?

                Thanks.
                • 5. Re: apex report string column to date
                  Denes Kubicek
                  Can you show your report SQL?

                  Denes Kubicek
                  -------------------------------------------------------------------
                  http://deneskubicek.blogspot.com/
                  http://www.apress.com/9781430235125
                  http://apex.oracle.com/pls/apex/f?p=31517:1
                  http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
                  -------------------------------------------------------------------