How to convert day to hours? 1

[Bartec]

Hi

I need change day to hour from my date for example:
I have following date format: 01 22:43:22 - where 01 is a day. Now I want to include (add) this day to hours so I want to have: 46:43:22.

Thanks for any help and tips

Best regards

Bartec

 

[carp]

It appears you don't have a date format, but rather a character string that represents a period of time.
A stored function might be of use here:

SQL> CREATE OR REPLACE FUNCTION my_date_format(p_in VARCHAR2)
RETURN VARCHAR2
AS
   l_string VARCHAR2(10);
BEGIN
   l_string := (24*to_number(substr(p_in,1,2)) + to_number(substr(p_in,4,2)))||substr(p_in,6,6);
   RETURN l_string;
END;
/

Function created.
SQL> select my_date_format('01 22:14:22') from dual;
MY_DATE_FORMAT('0122:14:22')
-------------------------------------------------------
46:14:22
SQL> select my_date_format('03 22:14:22') from dual;
MY_DATE_FORMAT('0322:14:22')
-----------------------------------------------------------
94:14:22

Of course, if you can have more than two digits' worth of days, you will need to change the code, but this should get you within striking distance.

 

[SantaMufasa]

And, as an alternative to Carp's fine function, to illustrate how to:

1) Accomplish your objective without a function
2) Leverage Oracle's implicit data-type conversions
3) Accommodate variable-length inputs

...here are more code:

select * from bartec;

DT
------------
0 10:17:10
1 14:22:40
10 03:50:60
100 10:20:30

select 24*substr(dt,1,instr(dt,' ')-1)
        + substr(dt,instr(dt,' ')+1,2)
       || substr(dt,instr(dt,':'),6) result
from bartec;

RESULT
-----------
10:17:10
38:22:40
243:50:60
2410:20:30

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Bartec]

Thanks a lot for your help guys!!!!!

Best regards!!

 

[carp]

Mufasa -
Any increase in efficiency gets a star in MY book!

 

[SantaMufasa]

You are very kind, my buddy!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Not kind so much as appreciative!