Date format issue

Newuser1

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,

Gianni Ceresa

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

@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

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

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

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

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]

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?