## Re: [PkOug] Time Calculate Problem

Expand Messages
• 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 1 of 7 , Feb 18, 2013
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 2 of 7 , Feb 19, 2013
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 3 of 7 , Feb 22, 2013
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 4 of 7 , Feb 26, 2013
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.