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

oracleoracle-11g-r2

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.

Best Answer

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

Related Solutions

Convert string to number from xml at server side

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.

Updating row of one table using rows of another table based on specific conditions

You could try MERGE instead of UPDATE. It is often faster and (in my opinion) more clear than UPDATE when updating a table based on another table

merge into FA_SUBLEDGER_MST s1
using (
    select SL_CODE, OFFICE_ID, OFFICE_TYPE
    from EMPLOYEE_MST
    where OFFICE_TYPE != 'DHB'
) emp
on (s1.SL_CODE = emp.SL_CODE)
when matched then update set 
    s1.OFFICE_ID = emp.OFFICE_ID,
    s1.OFFICE_TYPE = emp.OFFICE_TYPE
where sl.STATUS = 'A'
    and (s1.OFFICE_ID <> emp.OFFICE_ID
    or  s1.OFFICE_TYPE <> emp.OFFICE_TYPE);

Make sure that using-subquery doesn't return duplicate rows for each SL_CODE

Also your last example is missing the STATUS = 'A' condition

Best Answer

Related Solutions

Convert string to number from xml at server side

Updating row of one table using rows of another table based on specific conditions

Related Question