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.
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
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
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
fyi: these have identical formulas inside!
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.
Odbc driver returned an error (SQLExecDirectW).
Error Details
Please help me,
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
- 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'
ELSE SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3) END) ,
CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro" FROM 5 FOR 4)) )
- FirstDateFromPeriodChar ->
TO_DATETIME(TRIM(CONCAT ( CONCAT('01-' , CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro" FROM 0 FOR 3)= 'Ene' THEN 'Jan'
CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro" FROM 5 FOR 4)) )) , 'DD-Mon-YYYY')
- LastDayCurrentMonth ->
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
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!
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
Description: Join our Product Management team to learn about new features that are part of 26.R3 Application release of Fusion Data Intelligence (SCM). The session will cover updates on Supply Chain Management and Procurement. Presented by: Anil Sebastian - Senior Director, SCM Product Management, Fusion Data Intelligence…
Good day, please kindly assist. We are creating a graph to calculate the number of assets per milestones. However, the planners have only populated assets for only one milestone from the list of milestones the customer wants to track. Which gives an incorrect view as the project moves past different milestones that it has…
I scheduled the report in OTBI and the agent was run successfully. However, I do not receive the report after the run in my inbox. The addresses are properly recorded, and the report is in an excel format and saved as a pivot table in otbi. What can be the cause?
Anyone has any idea why the OAC documentation on custom viz is not available now? I was able to access till last week - https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsdv/build-package-extension.html. Does this mean OAC doesnt support custom viz or custom workbook extensions?
I made a parameter with letter range values… put it into a dashboard filter. Then I created an expression filter to bind it to my last name column in the table with the image I uploaded here. I am getting the following error??? Invalid Expression. Validation of parameter values failed. A general error has occurred. Message…
Does Oracle Analytics Server 2026 include AI features such as a built-in chatbot or AI Assistant? If not, are there any plans or indications that these AI capabilities will be introduced in a future OAS release?
