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
