nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Convert char to date format in obiee 11g?

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

I want to Convert char to date format in obiee 11g.

i have this date

'07/31/'||CAST(YEAR(CURRENT_DATE) AS CHAR)

I want to change char to date format.

please help me,

Thanks in advance,

A.kavya.

Find more posts tagged with

Accepted answers

All comments

Niamh O

Hi there,

Is this something you can add to your date dimension table? For example, in my date dimension, ever row has a column called: PYE_DATE_CHAR so that I can easily grab Previous Year End (eg '31 Dec 2015') in the desired format, on every row for a year it has the same value. Make your database do the work here especially the date table . Cleaner and better performance.

If that isn't an option or suit the requirement, use some substring there:

eg. '07/31/' || SUBSTRING(CAST(CURRENT_DATE AS CHAR) FROM 1 FOR 4)

(first CAST current_date as a CHAR to see where the YYYY is in the string).

Good luck, Niamh

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

as you , i have tried , i am getting..output like yyyy/mm/dd.

my date is as follows

'07/31/' || SUBSTRING(CAST("F_AA_LOAN_DISBURSE"."DISB_DATE" AS CHAR) FROM 1 FOR 4)

i want 2015/07/31. please help me,

Tahnks in advance,

a.kavya

Niamh O

Hi Kavya

Can you try with a brand new column with no special/custom data formatting? (use an existing string type column and change the formula)

If this doesn't fix, troubleshoot in the RPD:

What is the physical data column type of this field? Is it an Oracle table?

It has to be a DATE type for the above to work. And classified as a DATE or DATETIME in the RPD physical column attributes.

Is it being formatted anywhere in the physical or logical layers in the RPD?

Let me know - Niamh

Niamh O

2016-01-22 11_17_07-Oracle BI Answers.png

Niamh O

fyi: these have identical formulas inside!

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

If i use current_date, its working fine. If i use our table date like

CAST( SUBSTRING(CAST("F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 1 FOR 4) || '/07/31/' AS DATE)

i am getting below error.

View Display Error

Odbc driver returned an error (SQLExecDirectW).

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)

SQL Issued: SELECT 0 s_0, CAST(SUBSTRING(CAST("FIN_016"."F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 1 FOR 4)||'/07/31/' AS DATE) s_1 FROM "FIN_016" FETCH FIRST 65001 ROWS ONLY

Refresh

Please help me,

Thanks in advance,

A.kavya.

Niamh O

The below is based on my source Oracle database NLS date format as DD-MON-RR (Query on database- SELECT * > FROM nls_database_parameters WHERE PARAMETER = 'NLS_DATE_FORMAT').

Note this is not altered by the RPD connection pool, which is common.

So the date I assemble for Substrings and Casts has to match that before it can be Re-CAST as a DATE. ie 31-JUL-15 etc

Steps:

- drag a text field onto the report, any one

- Edit formula

- Paste in

CAST ( '31-Jul-'|| SUBSTRING( CAST( "F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 8 FOR 2) AS DATE)

- OK

- View results

cesar.advincula.o

- TO_DATETIME('2009-DEC-03', 'YYY... ->

Formula: TO_DATETIME('2009-DEC-03', 'YYYY-MON-DD')

- FirstDayCharDateNoConver ->

Formula: CONCAT('01-' , UPPER("FecCobro"."MesAñoCobro" ))

- MesCharConvert ->

Formula:

CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3)= 'Ene' THEN 'Jan'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Abr' THEN 'Apr'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Dic' THEN 'Dec'

ELSE SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) END

- CharDateConvert -> Formula:

CONCAT ( CONCAT('01-' , CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3)= 'Ene' THEN 'Jan'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Abr' THEN 'Apr'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Dic' THEN 'Dec'

ELSE SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) END) ,

CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro" FROM 5 FOR 4)) )

- FirstDateFromPeriodChar ->

Formula:

TO_DATETIME(TRIM(CONCAT ( CONCAT('01-' , CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3)= 'Ene' THEN 'Jan'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Abr' THEN 'Apr'

WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) = 'Dic' THEN 'Dec'

ELSE SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) END) ,

CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro" FROM 5 FOR 4)) )) , 'DD-Mon-YYYY')

- LastDayCurrentMonth ->

Formula:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Niamh O

A dedicated DATE dimension table would bring a lot of value in this case, all date fields you need relative to a day come ready made into queries, and be worth the development and RPD modeling.

Using star schema rules will get the best result out of OBIEE.

Very good luck exploring a solution!

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

sorry for late reply,

as you said, i have tried on that day, its working fine..

I have used like this formulae

CAST('31-Jul-' || TRIM(BOTH ' ' FROM CAST( YEAR("F_PL_BAL"."BUSINESS_DATE") AS CHAR) ) AS DATE)

Thank you very much,

A.kavya

Upcoming Event

Oracle AI World 2026
Where AI meets business outcomes Oracle AI World brings the Oracle community together to explore how AI and other technology innovations are driving real business outcomes across industries. Discover what’s new through: Mainstage keynotes to hear the latest directly from Oracle leadership Product demonstrations Customer…

Top Leaders