OBIEE TO_DATETIME function

TO_DateTime is secret function in OBIEE which is rarely known.

It is similar to TO_DATE function in SQL.

This function is not visible in OBIEE Answers, but you can use this.

Syntax :

TO_DATETIME('string1', 'DateTime_formatting_string')

Example:

case when "Test Result Dates/Times"."Test Begin Date"=to_datetime(CAST('1/1/1901' AS CHAR),'MM/DD/YYYY') THEN NULL ELSE "Test Result Dates/Times"."Test Begin Date"  END

HOW TO SET CUSTOM DATE FORMAT IN SQL USING NLS DATE FORMAT

We can execute the below script for displaying custom date format in sql. We can change the date format as per our requirement by changing the format string highlighted in bold.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

IF you want to set it by default while you login, you can write a trigger as below.

CREATE OR REPLACE TRIGGER DATE_TRIG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
END;

HOW TO SET UP CUSTOM DATETIME FORMAT IN SQL DEVELOPER

1. GO TO Menu -->Tools --> Preferences
2. In Preferences dialog --> select Database --> NLS from the left panel.
3. In NLS Parameter list --> set DD-MON-YY HH24:MI:SS in Date Format field.                                                                                                                                                                                                                 
4.