Convert varchar to date datatype in OTBI

Surya Anbazhagan

Summary:

Hi All,

I am looking for To_date function to convert from varchar to date datatype in OTBI but To_date function is not available.

Content (required):

Is it possible to convert to date datatype?

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

MandeepGupta

Hi Surya,

Can you please try to use CAST function?

Thanks.

Surya Anbazhagan

Yes. I tried but it is showing an error for to_date using cast.

Nathan CCC

Hi, "in OTBI but To_date function is not available" No I think this statement is false.

OBI does include a function to_datetime() to convert a string to a date. I agree it is not listed in the list of functions in the user interface when you select edit formula then fx(...) to insert funtion. But it is listed in your user guide logical sql refeference in the conversion functions.

I argue this is better than function cast because cast will only work if the string is in a format that works with he format configured in the setup as the default in the application server instance configuration which you cannot change.

For more information see Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10544-08 December 2014https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDIBAAB

Here you go.

select all 0 s_0
, to_datetime('1999-12-31','yyyy-mm-dd') s_1
, cast('1999-12-31' as date) s_2
, "Time"."Report End Date" s_3
from "OTBI Usage Real Time"
where ("Time"."Report End Date" = CURRENT_DATE)

Surya Anbazhagan

Thanks for the information.

User_ZTN0B

Thanks @Nathan CCC

A.JOSH

It was helpful. Thanks @Nathan CCC