SQL & PL/SQL :: Adding Two Numbers To Get Hours:minutes Format?

Jul 9, 2010

I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add

12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Subtract Two Numbers And Convert Result As Hours And Minutes

Oct 6, 2010

I want to convert numbers into hours and minutes.I have two numbers say like first number is 1234 and second number is 1235.4 now i want to find the different between these two numbers. so am subtracting number 2 - number 1

1235.4 - 1234 i will get 1.4 so the result of this 1.4 to be converted as hours and minutes like 1 hr and 40 minutes. How can i convert numbers in hrs and minutes.

View 7 Replies View Related

Forms :: Adding Hours And Minutes To Time

Feb 11, 2011

I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .

I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:

case :blk.duration_type
when 'HRS' then
:blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration);
when 'MINS' then
:blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);

However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .

View 1 Replies View Related

SQL & PL/SQL :: How To Get Hours / Minutes And Second From Date Datatype Field

Jan 14, 2012

I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :

SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;

VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00

ALTHOUGH THERE IS TIME . Its not zero .

View 3 Replies View Related

SQL & PL/SQL :: Finding Hours And Minutes Between Two Char Field?

Mar 24, 2012

I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.

start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes

If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example

start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)

View 2 Replies View Related

SQL & PL/SQL :: Total Number Of Hours And Minutes Of Given Dates

Sep 28, 2011

SELECT emp.emp_id, emp.ename, TRUNC (attendancedatetime),
TRUNC ( ( 86400
* (MAX (attendancedatetime) - MIN (attendancedatetime))
)
/ 60
)
- 60

[Code]...

Following is the out put:

EMP_IDENAME Date Min Hrs
10013Javed Iqbal09/20/2011 00:00:0036.007.00
10013Javed Iqbal09/21/2011 00:00:0027.007.00
10013Javed Iqbal09/22/2011 00:00:0049.007.00
10013Javed Iqbal09/23/2011 00:00:000.000.00
10013Javed Iqbal09/24/2011 00:00:000.000.00

i need the TOtal sum of Minutes and Hrs e.g 7+7+7=21 and also minutes but if minutes total increase from 60 minutes then it should add to hrs .how to get sum.

View 2 Replies View Related

Covert A Time Shown As 001200 (12 Hours) To 720 Minutes

Feb 10, 2012

I am trying to covert a time shown as 001200 (12 hours) to 720 mins. I have tried the following but get errors when executing:

(MTTD)>=TO_DATE('000000','HH24:MI:SS').

I have also tried .

MTTD = to_date('000000','HH24:MI:SS') =trunc(sysdate,'MM')*24*60; and neither is working.

What am I doing incorrectly?

View 1 Replies View Related

SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes

Jun 14, 2012

How to get Time Difference between two DateTime Columns in Oracle 10g ?

View 10 Replies View Related

PL/SQL :: Get Time In Hours Minutes And Seconds Subtract Between Two Varchars?

Oct 18, 2012

I have two varchar variable which has value like this

v_outpunch1:='17:50:00'
and v_Shifttime:='18:00:00'

this both time i am subtracting here and storing in another varchar variable which is like this.

v_EarlyLeaverstimeformat := LPAD((extract(hour from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(hour from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(minute from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(minute from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(second from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(second from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0');

it's not subtracting value correctly.

View 3 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

SQL & PL/SQL :: How To Compare Hours

Jan 10, 2011

I want to write a function that gets:

1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)

I got a table called EVENTS that got 2 fields:

1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)

and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:

input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date

but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..

I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Minutes Between Two Times

Oct 19, 2012

i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00

so in this it should return 360 minutes.

View 5 Replies View Related

SQL & PL/SQL :: Displaying 24 Hours Data

Dec 27, 2010

I have a table which will the following type of data

"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"

Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,

COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707

View 2 Replies View Related

SQL & PL/SQL :: Calculating The Business Hours

Aug 15, 2011

I have a field in Customers table called shipeddate....

I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...

But the business hrs of the store room are from 8am-5pm..

So when a shipped date is 4pm on MOnday

and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..

How can i achieve this...

View 2 Replies View Related

SQL & PL/SQL :: How To Get Correct Minute Between Two Hours

Oct 22, 2012

i am using one query but not getting correct minutes.

here is my query:

v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);

here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.

here i should get value 370.
but i am getting value 350.

View 3 Replies View Related

SQL & PL/SQL :: Calculating Working Hours?

May 8, 2010

I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours

The following query take 63.499 sec

SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,

[Code].....

View 6 Replies View Related

PL/SQL :: Convert Number To Hours

Jan 1, 2013

I want to convert below MS-SQL query in oracle 10g.

for eg:

Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable

Output:

9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes

View 8 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

I have a table with a date field. This field storage dates with hours like this:

01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...

I have to generate a report with the day and the frequency :

__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3

The block of the hour must be 1 hour (8-9,9-10 and so on)

how generate the hours range.

My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).

View 5 Replies View Related

PL/SQL :: How To Get Added Hours From Results

Jun 14, 2012

I am not getting added hours from results.

SELECT audittimestamp + interval (SELECT      EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.

View 7 Replies View Related

How To Round Time Up Or Down By 5 Minutes

Nov 29, 2006

is there a prebuilt function that will round say the time of a sysdate up or down 5 mins? so i entered 5:32pm i would want it to round it down to 5:30pm

View 1 Replies View Related

How To Display Time In Minutes

Oct 15, 2012

I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.

i.e (col1 + col 2)/60 = 34.87

what I need to do is make sure that when the number reaches 60 it moves on to 35.

View 2 Replies View Related

SQL & PL/SQL :: How To Subtract Minutes From Timestamp

Mar 31, 2010

I need below proc like...

procedure p1
(
i_time_min number -- minutes to be substracted from timestamp
)
is
v_end_timeinstamp timestamp(6);
begin

[code]....

The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.

View 6 Replies View Related

SQL & PL/SQL :: Count Records Per Every 10 Minutes

Jun 6, 2011

How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.

The sample data has been enclosed with it.

View 13 Replies View Related

SQL & PL/SQL :: Difference Of Dates And Get Minutes

Oct 25, 2012

I have a date in_sdate as In parameter defaulted to sysdate. Basing on this in_Sdate I calculate my start and end dates as:

v_sdate TRUNC (in_sdate, 'MI') - 15 / 1440 ;
v_edate := TRUNC (in_sdate, 'MI');

My procedure is run for every 15 minutes. Now suppose if I am running for old dat, then I should get the difference of dates by taking

v_old_Date := v_edate - in_Sdate;

Divide this by 15 , round that value and loop to run the procedure for that n times. My doubt is when I am saying

v_old_date := v_edate - in_sdate ; I am getting expression is of wrong type. How can I take the difference of the dates and get the minutes from that ?

View 2 Replies View Related

Archive Log Generated Each 4 Minutes?

Jul 2, 2012

in our database 10.0.2.4 with RAC archive log generated each 4 min , did this increase the performance of database and how i can fix it

View 14 Replies View Related

PL/SQL :: Date Subtraction Of Minutes?

Jun 14, 2012

how to get correct result for the time subtraction of minutes for the following query

select (to_date('14-06-2012 11:10:00','dd-mm-yyyy hh24:mi:ss') - to_date('14-06-2012 11:00:00','dd-mm-yyyy hh24:mi:ss'))*60*60 as
result from dual

RESULT
----------
25

i want the output as 10 minutes.

View 4 Replies View Related

DB Upgrade From 11.1 To 11.2 - SQL Statements Running For Hours

Feb 16, 2011

Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...

View 2 Replies View Related

Fetching Data Older Than 2 Hours

Mar 11, 2013

I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.

SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);

Is there anything wrong in the query.

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Business Days And Hours?

Sep 19, 2010

format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)

i need to calculate time elasped for that type of activity following are the rules:

(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM

only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be

6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..

View 4 Replies View Related

SQL & PL/SQL :: How To Validate Dynamic Hours Value Using Decode

Nov 1, 2013

I want to validate dynamic hours value using decode in sql...

If value is greater than 24PM it should display some statement...

if value is less than 0AM it should display some statment...

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved