A script to check the frequency of Oracle log switches | Oracle DBA tips
Posted by jpluimers on 2016/02/17
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
- A script to check the frequency of Oracle log switches | Oracle DBA tips.
- how to get a time part from Date datatype | Oracle Community.
- A tip for lazy Oracle users – type less with ANSI DATE and TIMESTAMP SQL syntax | Tanel Poder’s Performance & Troubleshooting blog.
The Wiert Corner - irregular stream of stuff 
Leave a Reply