Categories
- All Categories
- 89 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.1K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 51 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Convert char to date format in obiee 11g?
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.
Answers
-
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
0 -
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
0 -
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
0 -
0
-
fyi: these have identical formulas inside!
0 -
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 Codes: OPR4ONWY:U9IM8TAC:OI2DL65PState: 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 ONLYRefresh Please help me,
Thanks in advance,
A.kavya.
0 -
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
0 -
- 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)))
0 -
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!
0 -
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.kavya0