Convert Varchar to Date in OTBI Report

Anjan Polimera

I have converted varchar to date by using the code —>CAST(CAST("Incentive compensation - Attainment Real Time"."source transactional details"."Base_Tran_DFF_Start_date" as char) as date). This is working fine

Now I am facing the issue as invalid syntax when I add 24 months to the above code. Please suggest Valid syntax and code.

TIMESTAMPADD(SQL_TSI_MONTH, 24, CAST(CAST("Incentive compensation - Attainment Real Time"."source transactional details"."Base_Tran_DFF_Start_date" as char) as date)). —>Causing Issue

Rajasekhar Bandaru-Oracle

Hi Anjan

Please review the help center documentation about TIMESTAMPADD

https://docs.oracle.com/en/cloud/saas/taleo-enterprise/22a/otbou/c-datetimefunctions.html#id20131114135318992

Nathan CCC

Hi Anjan,

"I have converted varchar to date by using the code" You have NOT converted a string to a date. You have converted some unknown thing X "base_tran_dff_start_date" to a string then converted that string to a date. It makes no sense to me that you are casting a column to a string and then that string to a date. Why?

So to understand this we need to know what is the data type of the original column X "Base_Tran_DFF_Start_date" before you cast it to character? It it a datetime to begin with in the first place? If so, just remove the 2 casts? No need to to go from date to string then back to date.

Is this an additional information field you have configured using one or the descriptive flexible fields? Is it an attribute of data type varchar or number or date in Manage Descriptive Flexfields? If you dont know look in view log then look in the table definition in https://docs.oracle.com/en/cloud/saas/human-resources/24b/oedmh/index.html

select all 

TIMESTAMPADD(SQL_TSI_MONTH, 24, "source transactional details"."Base_Tran_DFF_Start_date") as s 

from "Incentive compensation - Attainment Real Time"

BTW casting a date to a string using cast(… as character) is dangerous because you are not in control of the date format the system will use. Make sure you do not assume in downstream code that a date will always be cast to a string using the same format in the future. Consider using function extract functions year( ) and month() and dayofmonth() perhaps left pad with zeros if you want to make a date a string in a "known" format.