ORA-01722: The specified number was invalid when truncating timestamp

Question

Query:

SELECT
    first_load_time
FROM
    v$sql;

Result:

2017-02-16/07:45:25
2017-02-17/06:47:36
2017-02-17/02:05:31
2017-02-15/17:39:13

Query:

SELECT
    to_char(FIRST_LOAD_TIME, 'YYYY-MM-DD')
FROM
    v$sql;  

Result:

ORA-01722: *Cause: The specified number was invalid.

SELECT
    trunc(FIRST_LOAD_TIME)
FROM
    v$sql;

ORA-01722: *Cause: The specified number was invalid.

Why does oracle give invalid number error when truncating the timestamp? I need to do a group by with date on this view.

Answer 1

The FIRST_LOAD_TIME column is actually a VARCHAR2 (which is a bit strange to me) so;

select to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') from v$sql

Will show it as a date by applying the function TO_DATE. Simply add a TRUNC to the beginning to get the value you want;

select TRUNC(to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS')) from v$sql