The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,685 other followers

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:

ORA-00904: "TIME": invalid identifier

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: