How To Trim Date from Date-Time Stamp - Hyperion

Question

I am using Hyperion Reporting Studio with Oracle and DB2 databases. We have fields that have an open date and a close date on them, so naturally the format would be 6/02/16 06:18:05 PM for either the open or close date fields. Is there a way to trim off the date section to be left with only the time stamp? I have tried TURNC as well as custom SQL and nothing is working. I know I can do it in excel using the =TIME formula. I could either add the computed item in the query or the results, im just not sure what to do.

Answer 1

Perhaps proper use of to_char?

    SQL> select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') full_date,
  2  to_char(sysdate,'yyyy-mm-dd') date_only,
  3  to_char(sysdate,'hh24:mi:ss') time_only
  4  from dual;

FULL_DATE            DATE_ONLY  TIME_ONL
-------------------- ---------- --------
03-Jun-2016 16:30:38 2016-06-03 16:30:38

For a more complete explanation of date/time formats in Oracle, see http://edstevensdba.com/oracle-data-types/understanding-oracle-date-formats/