OTBI Analysis - Convert Datetime value to Date (only)

Question

How do I convert a datetime value to a date only value:

"10/16/23 9:24 PM" to "10/16/23"

Should be simple, right? 😏

Dir_Pal · Accepted Answer

to_date(to_char(sysdate, 'YYYY/MM/DD') ,'YYYY/MM/DD')

hope this helps

Nathan CCC · Accepted Answer

Hi Mike,

Unfortunately the SQL syntax is different. Functions to_date and to_char are NOT valid. You need to use the correct version of SQL. You are using "logical" sql in an analysis in OTBI or in a data model in a report in OTBI using data source OBIEE. Here is a link to review the functions in the sql language reference user guide.

Oracle® Fusion Middleware

Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition

Release 12c (12.2.1.3.0)

E80604-01

August 2017

https://docs.oracle.com/middleware/12213/biee/BIESQ/toc.htm

My preference to convert a date time to a date is to use function cast

select all 0 s_0
, "Ledger"."Ledger Creation Date" as created_datetime
, cast("Ledger"."Ledger Creation Date" as date) as created_dateonly
from "General Ledger - Period Status Real Time"
fetch first 7 rows only

Dir_Pal · Accepted Answer

Hi @Nathan CCC  if its data model then isn`t it more simple to change in properties itself