A wile ago, I had a this error when trying to get the TIME portion of a DATE column:

This doesn’t work in Oracle, even though when you search for Oracle convert DATE to TIME you end up at this page listing TIME as a function: 12.7 Date and Time Functions. Alas, that page is for MySQL which is owned by Oracle for a while now.

Back to the query which was like this where date_column was of type DATE.

SELECT id, date_column, TIME (date_column) FROM some_table

That DATE type actually stores date+time, and since it was filled with Delphi TTime values, the date parts would always be “1899-12-30” (yes, I like ANSI DATE and TIMESTAMP formats). Oracle doesn’t get that, so I wanted to get the time portion.

Solutions:

SELECT id, date_column, TO_CHAR (date_column, 'HH24:MI:SS'), date_column - TRUNC(date_column), 24 * (date_column - TRUNC(date_column)) FROM some_table

Since it was meant for calculation, I used date_column - TRUNC(date_column) as that gives the fraction of the day.

–jeroen

via