This discussion is archived
5 Replies Latest reply: Apr 25, 2013 4:40 AM by Denes Kubicek RSS

apex report string column to date

Irha10 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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
    -------------------------------------------------------------------

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points