Hi All,
I'm re-asking this question differently and hopefully someone will be able to help me out
As a quick test I did the following and hopefully it can be replicated
create table t ( test1 varchar2(22) );
The data that I will be working with, the datetime will be entered as per below.
insert into t values ( '05/22/2017 02:20:55 AM' );
When I try now to extract HH:MI:SS using
select to_char(test1, 'hh:mi:ss')
from tI get the following error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Using this test example what can I use to successfully extract the time part only in HH:MM:SS?
In addition
My date format under Database --> NLS is MM/DD/YYYY HH24:MI:SS
Since you are storing the date as a string (bad idea) you can use the standard string operations to extract it.
SQL> insert into t values ( '05/22/2017 02:20:55 AM' );
1 row created.
SQL> select substr(test1,12,8) from t;
SUBSTR(TEST1,12,8)
--------------------------------
02:20:55
1 row selected.
Alternatively, you can convert the string to a date, at which point, the to_char functions become available because to_char does *not* take a string as input, but a date or a number (which is why you're getting that error).
SQL> select to_char(to_date(test1,'mm/dd/yyyy hh:mi:ss am'),'hh24:mi:ss') from t;
TO_CHAR(
--------
02:20:55
Ideally, you want the table column to be defined as a DATE, at which point, to_char will work directly on the column.