CODEWITHSUNDEEP
oracle-databasedatetimeoracle-sqldeveloper
MontyPython
MontyPython

Reputation: 2994

How to extract only Time from a DateTime field in Oracle SQL Developer?

I tried To_Timestamp and other methods for SQL Developer but only this one worked fine for me.

Select To_Number(To_Char(DateTime_FieldName, 'HH24'))
    || ':' || to_number(to_char(DateTime_FieldName, 'MI'))
    || ':' ||to_number(to_char(DateTime_FieldName, 'SS'))
from TABLE_NAME

Is there a better solution to this?

Upvotes: 16

Views: 112285

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

Assuming your goal is to generate a string representing the time (which is what the query you posted returns despite the extraneous to_number calls)

SELECT to_char( <<column_name>>, 'HH24:MI:SS' )
  FROM table_name

If you want to return a different data type, you'd need to tell us what data type you want to return. If, for example, you really want to return an INTERVAL DAY TO SECOND

SELECT numtodsinterval( <<column name>> - trunc(<<column name>>), 'day' )
  FROM table_name

Upvotes: 45

Related Questions