Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert VARCHAR2 into Number

Tags:

sql

oracle

I have a table with a column named duration. Its data type is VARCHAR2.

I want to sum the column duration.

00:56:30
02:08:40
01:01:00

Total=> 04:05:10

How can I do this using ANSI SQL or Oracle SQL?

like image 459
Kristiana Avatar asked May 29 '13 11:05

Kristiana


1 Answers

You can separate the hours, mins and seconds using SUBSTR, then SUM it up and finally use NUMTODSINTERVAL function to convert it into INTERVAL type.

SELECT NUMTODSINTERVAL (SUM (total_secs), 'second')
  FROM (SELECT   SUBSTR (duration, 1, 2) * 3600
               + SUBSTR (duration, 4, 2) * 60
               + SUBSTR (duration, 7, 2) total_secs
          FROM user_tab);
like image 55
Noel Avatar answered Oct 04 '22 00:10

Noel