Convert text field to date to use for calculation

Dawn Phipps

Content

We have a UDF field which is populated by the candidate on the recruiting side in a form.  The input is a DATE. There is no other choice on how to bring in the date and comes over as TEXT into OBIEE in the candidate UDF.   Like this --->>>  04-06-2015   I cannot use this to perform calculations with other dates for aging.  How can I convert a text field to date?  I found this but it did not work....

http://obieetutorialguide.blogspot.com/2013/01/to-get-age-between-two-days-in-obiee.html   

http://www.bravesoft.com/blog/?p=682

Also, what is the formula to do aging between 2 dates in OBIEE?  I want to calculate the number of days between 2 dates in the report.  Example: "current_date" and "consent_date"  (how many days in between)

Caroline Gladwin-Oracle

Try using CAST - this function changes the data type of an expression or a null literal to another data type. The following are the supported data types to which the value can be changed:

 

CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING

 

Depending on the source data type, some destination types are not supported.

example - CAST("UDF field name" as TIMESTAMP)

Then you can use timestampdiff to get the number of periods between two items.

Regards Caroline

Rachel Martorelli-Oracle

If cast doesn't work try this formula

 

EVALUATE('TO_DATE(%1,%2)' AS DATE,"Req Date"."Date",'MM-DD-YYYY')

 

To calculate time between to days I like the Timestampdiff() function.  For example timestampdiff(sql_tsi_day, EVALUATE('TO_DATE(%1,%2)' AS DATE,"Req Date"."Date",'MM-DD-YYYY'), now())

Dawn Phipps

I tried timestampdiff but not with that exact syntax you have here Rachel.  This worked!!! Thank you so much!!!  I am going to save this syntax as many of our reports in BO have AGING columns in them so I am going to need this!  Much appreciated..