Converting a date into seconds

[ecasadella]

I have a field that is varchar2(11) which contains the time of an elapsed time like '36:12:33' hoour:min:secs , and I need to display the full time in seconds, does any body know how to achieve it?

Thanks.

EDC
--------------------------------------
This is Unix-Land. In quiet nights, you can hear the Windows machines
reboot.

 

[LKBrwnDBA]

Try:

SQL>var MyTime Varchar2(10);
SQL>exec :MyTime:='36:12:33';

PL/SQL procedure successfully completed.

SQL>select (substr(:MyTime,1,2)*24+substr(:MyTime,4,2))*60
  2         +substr(:MyTime,7,2) elapsed_sec
  3    from dual
  4  /

ELAPSED_SEC
-----------
      52593

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ecasadella]

It works great, thanks!

EDC
--------------------------------------
This is Unix-Land. In quiet nights, you can hear the Windows machines
reboot.

 

[Beilstwh]

You are using the following query.

SQL>select (substrMyTime,1,2)*24+substrMyTime,4,2))*60
2 +substrMyTime,7,2) elapsed_sec
3 from dual
4 /

ELAPSED_SEC
-----------
52593


However, you are multiplying by 24, Shouldn't the query be

SQL>select ((substrMyTime,1,2)*60)+substrMyTime,4,2))*60
2 +substrMyTime,7,2) elapsed_sec
3 from dual
4 /


1 select (36*60*60)+(12*60)+(33) elapsed_sec
2* from dual
SQL> /

ELAPSED_SEC
-----------
130353

Bill
Oracle DBA/Developer
New York State, USA

 

[LKBrwnDBA]

Good catch!

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb