Sorry, an error occurred while loading the content.

## Time Calculate Problem

Expand Messages
• Aslamualakum, I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and
Message 1 of 7 , Feb 15, 2013
• 0 Attachment

Aslamualakum,

I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

T1   varchar2(5);

T2   varchar2(5);

Diff  varchar2(5);

 T1 T2 Calculate  Difrrence time 07:30 08:10 07:30 08:00 07:30 08:08 Total ? ?

Regards

Zahid Waseem
• Dear, Below query will help you. select trunc(mod((to_date( 18-feb-2013 07:30 , dd-mon-yyyy hh24:mi ) - to_date( 18-feb-2013 07:00 , dd-mon-yyyy hh24:mi )) *
Message 2 of 7 , Feb 17, 2013
• 0 Attachment
Dear,
Below query will help you.

select trunc(mod((to_date('18-feb-2013 07:30', 'dd-mon-yyyy hh24:mi') -
to_date('18-feb-2013 07:00', 'dd-mon-yyyy hh24:mi')) * 24,
24)) || ':' ||
trunc(mod((to_date('18-feb-2013 07:30', 'dd-mon-yyyy hh24:mi') -
to_date('18-feb-2013 07:00', 'dd-mon-yyyy hh24:mi')) * 24 * 60,
60))
from dual;

Regards,
Mirza Zeeshan Baig

--- In PkOug@yahoogroups.com, zahid <zaidi_005@...> wrote:
>
> Aslamualakum,
> I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:
> T1   varchar2(5); T2   varchar2(5);  Diff  varchar2(5);
>
>
>
>
>
>
>
>
>
> T1T2
> Calculate  Difrrence time
>
>
>
> 07:30
> 08:10
>
>
>
>
>
> 07:30
> 08:00
>
>
>
>
>
> 07:30
> 08:08
>
>
>
>
> Total
> ?
> ?
>
>
>
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
> Zahid Waseem
>
• W salam You first have convert hours into minute, then calculate total minutes Fallowing example will help you Suupose you have a table test12 having a column
Message 3 of 7 , Feb 17, 2013
• 0 Attachment
W'salam
You first have convert hours into minute, then calculate total minutes

Fallowing example will help you
Suupose you have a table test12 having a column of time

select * from test12

10:29

10:15

10:16

Now consider below query

select trunc(total_minutes/60) as the_hour,
mod (total_minutes ,
60) as the_minute

from
(
select Sum(To_Number(substr(hour,
1,2)))*60 +Sum(To_Number(substr(hour,4,5))) total_minutes
from   test12
)

output

THE_HOUR    THE_MINUTE

31            00

Abid

On Sat, Feb 16, 2013 at 11:14 AM, zahid wrote:

Aslamualakum,

I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

T1   varchar2(5);

T2   varchar2(5);

Diff  varchar2(5);

 T1 T2 Calculate  Difrrence time 07:30 08:10 07:30 08:00 07:30 08:08 Total ? ?

Regards

Zahid Waseem

--
Regards

ABID HUSSAIN

Oracle Technical Consultant
Sapphire Consulting Services
• Dear Zahid, If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use
Message 4 of 7 , Feb 18, 2013
• 0 Attachment
Dear Zahid,

If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.

Best Regards
Imran Hafeez
Assistant Manager IT
The Institute of Chartered Accountants of Pakistan
Phone No: 99-251635
Mobile No: 0300-2299749
Email: manirose@..., famnis@...

From: zahid <zaidi_005@...>
To: PkOug@yahoogroups.com
Sent: Saturday, February 16, 2013 11:14 AM
Subject: [PkOug] Time Calculate Problem

Aslamualakum,

I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

T1   varchar2(5);
T2   varchar2(5);
Diff  varchar2(5);

 T1 T2 Calculate  Difrrence time 07:30 08:10 07:30 08:00 07:30 08:08 Total ? ?

Regards

Zahid Waseem

• Thanks i will try  Regards Zahid Waseem ... From: Hafeez Imran Subject: Re: [PkOug] Time Calculate Problem To: PkOug@yahoogroups.com
Message 5 of 7 , Feb 19, 2013
• 0 Attachment
Thanks i will try

Regards

Zahid Waseem

--- On Mon, 2/18/13, Hafeez Imran <famnis@...> wrote:

From: Hafeez Imran <famnis@...>
Subject: Re: [PkOug] Time Calculate Problem
To: "PkOug@yahoogroups.com" <PkOug@yahoogroups.com>
Date: Monday, February 18, 2013, 3:12 PM

Dear Zahid,

If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.

Best Regards
Imran Hafeez
Assistant Manager IT
The Institute of Chartered Accountants of Pakistan
Phone No: 99-251635
Mobile No: 0300-2299749
Email: manirose@..., famnis@...

From: zahid <zaidi_005@...>
To: PkOug@yahoogroups.com
Sent: Saturday, February 16, 2013 11:14 AM
Subject: [PkOug] Time Calculate Problem

Aslamualakum,

I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

T1   varchar2(5);
T2   varchar2(5);
Diff  varchar2(5);

 T1 T2 Calculate  Difrrence time 07:30 08:10 07:30 08:00 07:30 08:08 Total ? ?

Regards

Zahid Waseem

• Dear, CREATE OR REPLACE FUNCTION SF_GET_PROD_STOPPAGE (V_TIMEIN IN VARCHAR2, V_TIMEOUT IN VARCHAR2) RETURN VARCHAR2 AS V_STOPPAGE VARCHAR2(6); V_ST_HR
Message 6 of 7 , Feb 22, 2013
• 0 Attachment
Dear,

CREATE OR REPLACE FUNCTION "SF_GET_PROD_STOPPAGE"
(V_TIMEIN IN VARCHAR2,
V_TIMEOUT IN VARCHAR2)
RETURN VARCHAR2 AS
V_STOPPAGE VARCHAR2(6);
V_ST_HR varchar2(6);
V_ST_MI varchar2(6);

BEGIN

V_ST_HR := EXTRACT(HOUR FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));
V_ST_MI := EXTRACT(MINUTE FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));

V_STOPPAGE := LTRIM(TO_CHAR(V_ST_HR, '00')) || '.' || LTRIM(TO_CHAR(V_ST_MI, '00'));

RETURN TRIM(V_STOPPAGE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END SF_GET_PROD_STOPPAGE;

--- In PkOug@yahoogroups.com, zahid <zaidi_005@...> wrote:
>
> Thanks i will tryÂ
>
> Regards
>
>
>
>
>
>
>
> Zahid Waseem
>
> --- On Mon, 2/18/13, Hafeez Imran <famnis@...> wrote:
>
> From: Hafeez Imran <famnis@...>
> Subject: Re: [PkOug] Time Calculate Problem
> To: "PkOug@yahoogroups.com" <PkOug@yahoogroups.com>
> Date: Monday, February 18, 2013, 3:12 PM
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Â
>
>
>
>
>
>
>
>
>
> Dear Zahid,
>
> If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
> Â Best Regards
> Imran Hafeez
> Assistant Manager IT
> The Institute of Chartered Accountants of Pakistan
> Phone No: 99-251635
> Mobile No: 0300-2299749
> Email: manirose@..., famnis@...
> From: zahid <zaidi_005@...>
> To: PkOug@yahoogroups.com
> Sent: Saturday, February 16, 2013 11:14 AM
> Subject: [PkOug] Time Calculate Problem
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Â
>
>
>
>
>
>
>
>
>
> Aslamualakum,
> I have two columns Â having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:
> T1 Â  varchar2(5);Â T2 Â  varchar2(5);Â Â DiffÂ  varchar2(5); Â
> Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â
>
>
>
>
>
>
>
>
>
> T1T2CalculateÂ  Difrrence time
>
>
>
> 07:30
> 08:10
>
>
>
>
>
> 07:30
> 08:00
>
>
>
>
>
> 07:30
> 08:08
>
>
>
>
> Total
> ?
> ?
>
>
>
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
> Zahid Waseem
>
• Isn’t it pretty simple? check below SQL, you won’t need to have additional UDF. SELECT TO_CHAR(TO_TIMESTAMP(OUTTIME, HH24:MI ) - TO_TIMESTAMP(INTIME,
Message 7 of 7 , Feb 26, 2013
• 0 Attachment
Isn’t it pretty simple? check below SQL, you won’t need to have additional UDF.

SELECT TO_CHAR(TO_TIMESTAMP(OUTTIME, 'HH24:MI') - TO_TIMESTAMP(INTIME, 'HH24:MI'),'HH24.MI')
FROM (SELECT '23.00' INTIME, '23.30' OUTTIME FROM DUAL);

Your SQL Can be pretty straight like this:

SELECT TO_CHAR(TO_TIMESTAMP(YOUR_OUTTIME, 'HH24:MI') - TO_TIMESTAMP(YOUR_INTIME, 'HH24:MI'),'HH24.MI')
FROM YOUR_TABLE;

BR,
Kawish Siddiqui -
+1 832 708 6862

Sent: Friday, February 22, 2013 11:35 PM
Subject: [PkOug] Re: Time Calculate Problem

Dear,

CREATE OR REPLACE FUNCTION "SF_GET_PROD_STOPPAGE"
(V_TIMEIN IN VARCHAR2,
V_TIMEOUT IN VARCHAR2)
RETURN VARCHAR2 AS
V_STOPPAGE VARCHAR2(6);
V_ST_HR varchar2(6);
V_ST_MI varchar2(6);

BEGIN

V_ST_HR := EXTRACT(HOUR FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));
V_ST_MI := EXTRACT(MINUTE FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));

V_STOPPAGE := LTRIM(TO_CHAR(V_ST_HR, '00')) || '.' || LTRIM(TO_CHAR(V_ST_MI, '00'));

RETURN TRIM(V_STOPPAGE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END SF_GET_PROD_STOPPAGE;

--- In mailto:PkOug%40yahoogroups.com, zahid wrote:

>
> Thanks i will tryÂ
>
> Regards
>
>
>
>
>
>
>
> Zahid
Waseem
>
> --- On Mon, 2/18/13, Hafeez Imran wrote:
>
> From: Hafeez Imran
> Subject: Re: [PkOug] Time Calculate
Problem
> To: "
href="mailto:PkOug%40yahoogroups.com">mailto:PkOug%40yahoogroups.com" mailto:PkOug%40yahoogroups.com>
>
Date: Monday, February 18, 2013, 3:12 PM
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Â
>
>
>
>
>
>
>
>
>
> Dear Zahid,
>
> If you want to get exact results, you should have datetime column in
your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
> Â Best Regards
> Imran Hafeez
> Assistant Manager
IT
> The Institute of Chartered Accountants of Pakistan
> Phone No:
99-251635
> Mobile No: 0300-2299749
> Email: manirose@...,
famnis@...
> From: zahid
> To:
href="mailto:PkOug%40yahoogroups.com">mailto:PkOug%40yahoogroups.com
> Sent: Saturday, February 16, 2013 11:14 AM
> Subject: [PkOug]
Time Calculate Problem
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Â
>
>
>
>
>
>
>
>
>
> Aslamualakum,
> I have two
columns Â having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:
> T1 Â  varchar2(5);Â T2 Â
varchar2(5);Â Â DiffÂ  varchar2(5); Â
> Â  Â  Â
Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â  Â
>
>
>
>
>
>
>
>
>
> T1T2CalculateÂ
Difrrence time
>
>
>
> 07:30
> 08:10
>
>
>
>
>
> 07:30
> 08:00
>
>
>
>
>
> 07:30
> 08:08
>
>
>
>
> Total
> ?
> ?
>
>
>
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
> Zahid
Waseem
>

Your message has been successfully submitted and would be delivered to recipients shortly.