I want to convert a VARCHAR2-value like '-28:15:00' to INTERVAL.

With a literal value, this works:

select interval '-09:11:36' hour to second from dual;

However, this does not (ORA-00923: FROM keyword not found where expected):

select interval MY_VARCHAR hour to second from MY_TABLE;
--comparable to select interval to_char(sysdate, 'hh:mm:ss') hour to second from dual;

My assumption is that the literal value is implicitly cast while the explicit varchar-value from MY_VARCHAR (or char from to_char respectively) is not valid between "interval" and "hour".

CAST like this does not work (ORA-00963: unsupported interval type):

select cast(MY_VARCHAR as interval hour to second) from MY_TABLE;
--comparable to select cast('09:11:36' as interval hour to second) from dual;

What does work is concatenating '0 ' as the day-value and cast it to INTERVAL DAY TO SECOND:

select cast('0 ' || '09:11:36' as interval day to second) from dual;

However this only works for positive values, and as long as the value for hour is below 24. Is there a better solution than dissecting the VARCHAR-value with CASE, SUBSTR and so on?

CodePudding user response：

You need the minus sign before the days to cast it to an interval:

SELECT value,
       CAST( REGEXP_REPLACE(value, '^(-)?', '\10 ') AS INTERVAL DAY TO SECOND )
         AS interval_value
FROM   table_name

or, using simple string functions, which slightly more to type but probably more efficient (as regular expressions are slow):

SELECT value,
       CAST(
         CASE
         WHEN value LIKE '-%'
         THEN '-0 ' || SUBSTR(value, 2)
         ELSE '0 ' || value
         END
         AS INTERVAL DAY TO SECOND
       ) AS interval_value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT '-09:11:36' FROM DUAL UNION ALL
  SELECT '09:11:36' FROM DUAL;

Both output:

... so your first query gets ORA-00923.

And the cast() function only supports certain conversions:

Note 1: Datetime/interval includes DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.

so you can't cast(... as interval hour to second), and that will throw ORA-00963.