CODEWITHSUNDEEP
sqloracle-database
Chirag Raj
Chirag Raj

Reputation: 35

Oracle SQL developer shows invalid number for the code below

I'm trying to subtract two columns which holds "systimestamp" as the date and time value. i'm getting

'ORA-01722: invalid number'

as the error message. Would be of great help if somebody can help out.

I tried googling this error and it says the character string might not be a valid literal . It works fine for other records , only problem is when i'm trying to subtract it.

SELECT ETL_BATCH_ID,
       ETL_BATCH_GROUP_NAME,
       TO_CHAR(BATCH_START_TS,'DD-MON-YY')
       || ' '
       ||TO_CHAR(BATCH_START_TS,'HH24:MI:SS') "BATCH_START_TS",
       TO_CHAR(DW_DM_END_TS ,'DD-MON-YY')
       ||' '
       ||TO_CHAR(DW_DM_END_TS , 'HH24:MI:SS') "DW_DM_END_TS" , 
       (TO_CHAR(DW_DM_END_TS , 'HH24:MI:SS')) - (TO_CHAR(BATCH_START_TS,'HH24:MI:SS')) "COMPLETION_TIME"              
FROM   bi_etl.bi_etl_batch
WHERE ETL_BATCH_GROUP_NAME = 'CMD';

For ex ,

BATCH_START_TS    || DW_DM_END_TS     || COMPLETION_TIME 
01-OCT-19 3:18:00 ||01-OCT-19 3:20:00 || 00:02:00

So the completion time is (DW_DM_END_TS) - (BATCH_START_TS) = COMPLETION_TIME But it's throwing the particular error as shown above

Upvotes: 2

Views: 202

Answers (3)

Popeye
Popeye

Reputation: 35920

For an exact result, You need to retrieve hour, minute and second from the difference in the timestamp , Like the following:

SELECT
    ETL_BATCH_ID,
    ETL_BATCH_GROUP_NAME,
    BATCH_START_TS,
    DW_DM_END_TS,
    LPAD(EXTRACT(HOUR FROM COMPLETION_TIME), 2, 0)
    || ':'
    || LPAD(EXTRACT(MINUTE FROM COMPLETION_TIME), 2, 0)
    || ':'
    || LPAD(ROUND(EXTRACT(SECOND FROM COMPLETION_TIME)), 2, 0) AS COMPLETION_TIME
FROM
    (
        SELECT
            ETL_BATCH_ID,
            ETL_BATCH_GROUP_NAME,
            TO_CHAR(BATCH_START_TS, 'DD-MON-YY')
            || ' '
            || TO_CHAR(BATCH_START_TS, 'HH24:MI:SS') "BATCH_START_TS",
            TO_CHAR(DW_DM_END_TS, 'DD-MON-YY')
            || ' '
            || TO_CHAR(DW_DM_END_TS, 'HH24:MI:SS') "DW_DM_END_TS",
            DW_DM_END_TS - BATCH_START_TS "COMPLETION_TIME"
        FROM
            BI_ETL.BI_ETL_BATCH
        WHERE
            ETL_BATCH_GROUP_NAME = 'CMD'
    );

Example:

SQL> SELECT
  2      LPAD(EXTRACT(HOUR FROM DIFF), 2 , 0) || ':' ||
  3      LPAD(EXTRACT(MINUTE FROM DIFF), 2 , 0) || ':' ||
  4      LPAD(ROUND(EXTRACT(SECOND FROM DIFF)), 2 , 0) as diff
  5  FROM
  6      (
  7          SELECT
  8              SYSTIMESTAMP - ( SYSTIMESTAMP - 2 / 1440 ) AS DIFF
  9          FROM
 10              DUAL
 11      );

DIFF
--------------------------
00:02:01

SQL>

Cheers!!

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Apply Substraction directly on those timestamp values :

select DW_DM_END_TS - BATCH_START_TS as "COMPLETION_TIME"              
  from bi_etl_batch;

COMPLETION_TIME
-000000000 00:02:00.000000

Demo

Substraction is impossible and has no sense among two string type values.

Upvotes: 2

Needle file
Needle file

Reputation: 518

U cann't any arifmetic operation on char. so change To_char -> To_date, or remove to_char if fields is date

Upvotes: 0

Related Questions