Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Date format issue

Received Response
305
Views
7
Comments
Newuser1
Newuser1 Rank 4 - Community Specialist
edited August 2024 in Oracle Analytics Cloud and Server

Hi Team,

We have bill_date field data type is varchar and it contains 2 formats of data

  1. 20180827T120100.000 GMT ---  for few records
  2. 3/21/2019 12:00 AM --- for few records

We have to convert these data in MM\DD\YYYY format in RPD, issue we are facing is

If we use below date conversion:

TO_DATETIME (substring ("Bill"."Date" from 1 for 8), 'YYYY/MM/DD')

Then it will consider

20180827

3/21/201

Error:

[nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. (HY000)

TO_DATETIME (substring ("Bill"."Date" from 1 for 10), 'YYYY/MM/DD')

Then it will consider

20180827T1

3/21/2019

Error :

[nQSError: 17001] Oracle Error code: 1861, message: ORA-01861: literal does not match format string at OCI call OCIStmtFetch. (HY000)

Version : OBIEE 12c

How to handle format issue can someone assist.

Thanks,

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod 
    Raa.BI wrote:We have to convert these data in MM\DD\YYYY format in RPD, issue we are facing is

    You don't want that, at the best this is a workaround waiting the fix to be done where it has to be done: database.

    In the RPD it means the formula will be applied on every single row of your queries, no need to look any further for problems: slow, no index usage etc.

    Your 2 formats have a clearly different length, add a CASE WHEN checking the length of the string and apply 2 logic in your substring-to_datetime.

    You can't expect a single mask format to match 2 different string formats.

  • Newuser1
    Newuser1 Rank 4 - Community Specialist

    @Gianni,

    Thanks for your response, for now we have to do this in RPD level later it will be taken care from back end I will try with case statement.

    Thanks,

  • Newuser1
    Newuser1 Rank 4 - Community Specialist

    I’m trying to convert below DB query into OBI logic facing issue to convert To_date function was there any different syntax I have use in OBI.

    Case

    when instr(bill_DATE,'/') > 0

    then to_date(bill_DATE ,'MM/DD/YYYY HH:MI AM')

    Thanks,

  • Gianni Ceresa
    Gianni Ceresa Mod 
    Raa.BI wrote:... facing issue to convert To_date function was there any different syntax I have use in OBI.

    In your first post you used TO_DATETIME, so you probably have to use that function as OBIEE doesn't have a TO_DATE function.

  • Newuser1
    Newuser1 Rank 4 - Community Specialist

    Sql query working in DB, trying convert in report facing below issue

    SQl Query:

    Select bill_dat,

    case

    when instr(bill_dat,'/') > 0

    then to_date(bill_dat ,'MM/DD/YYYY HH:MI AM')

    else to_date (substr(bill_dat, 1,8),'YYYYMMDD')

    end

    from sample

    OBI Logic:

    case

    when INSTR("sample"."bill_dat",'/') > 0

    then To_DateTime("Sample"."bill_ dat, 'MM/DD/YYYY HH:MI AM')

    else To_DateTime(SUBSTRING("Sample"."bill_dat" from 1 for 8),'YYYYMMDD')

    END

    Error facing:

    [nQSError: 43113] Message returned from OBIS. (HY000)

    [nQSError: 27042] Function INSTR is not defined by administrator. (HY000)

  • Gianni Ceresa
    Gianni Ceresa Mod

    OBIEE doesn't speak SQL but LSQL.

    Every place letting you enter a formula as selectors to show you what formulas exists.

    The forum doesn't let me upload a picture right now (something must be wrong).

    If you look into String function you find one called "Position", have a look.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Loads of talk in this thread but how about looking at what's really stored in the DB and checking what your NQSConfig.ini is set to?