0

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.

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
  • Blimey! My love for Oracle just grows by the day – Kshitiz Sharma Feb 17 '17 at 14:52
  • It is a strange one that's for sure. – BriteSponge Feb 17 '17 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.