Forms :: Calculate Difference Time Between Two Fields

Mar 24, 2011

I have two different date in my payroll software,

1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings

As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement

SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;

but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields

Apr 9, 2013

I need the query to calculate minutes from difference of to number fields. Test case is as below.

DROP TABLE tmp;

CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......

CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32

Required output is

CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238

View 8 Replies View Related

SQL & PL/SQL :: How To Calculate Time Difference

Aug 10, 2003

I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.

View 4 Replies View Related

SQL & PL/SQL :: Calculate Time Difference Between 2 Date

Nov 29, 2011

during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?

CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Dates At Same Time?

Jan 10, 2012

how to calculate the difference between multiple dates at the same time..

Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;

Giving me an error...

ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..

View 8 Replies View Related

SQL & PL/SQL :: Calculate With Calculated Fields?

Feb 24, 2010

can I use calculated fields (b) for another calculation (c) in the same select?

select 10*a as b,
10*b as c
from tab

The code gives an error, but how can I do this?

View 12 Replies View Related

Forms :: How To Capture Date And Time In Separate Fields

May 8, 2012

I want to capture date and time in separate fields from user and save it in single column in table

example: user enters date and time
Date : 08/05/20212
Time : 18:50:00

At the time of save I want to save [08/05/2012 18:50:00].

View 1 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Numbers

Feb 28, 2013

I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.

create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)

insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);

insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);

Expected Output:

col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222

View 6 Replies View Related

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.

CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,  
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
        
FROM tableT

WHERE
need_date <=  (Case when estimated_delivery > ' ' THEN  estimated_delivery ELSE need_date END)

ORDER BY Date_Diff ASC

View 6 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;

that i have worte..

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Two Date Type Fields

Aug 15, 2011

I'm having trouble comparing and subtract two date type fields, can not get a significant result.

e.g.

select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;

View 4 Replies View Related

SQL & PL/SQL :: Calculate Outage Time For Each Day

Jul 8, 2010

We have a table which contains the outage data as below,

TAG CI_NAME OUTAGE_START OUTAGE_END
IM10366 FD0004 06-jul-2010 10:00:00 10-jul-2010 10:00:00

so from the above record, the outage is from 6th - 10th July. So for the given TAG & CI_NAME, we have to calculate the outage for day. So the above record should be split as below

TAG CI_NAME O_START O_END TA_PERIOD OUT_HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 06-jul-2010 14HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 07-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 08-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 09-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 10-jul-2010 10HRS

View 1 Replies View Related

SQL & PL/SQL :: How To Calculate Time Diff Between LAST_ANALYZED Dates

May 27, 2011

Oracle Version = 8.1.7.4

How to change the SQL below to include a calculated datetime diff between two consecutive dates?

I believe I should be using what SQL gurus will call hierarchichal/analytical queries? Any good website/link to learn hierarchical/analytical queries.

select last_analyzed, monitoring
from dba_tables where owner = 'TEST'

LAST_ANALYZED MON
-------------------- ---
24-MAY-2011 18:25:05 YES
24-MAY-2011 19:15:34 YES
24-MAY-2011 22:21:33 YES
24-MAY-2011 23:10:42 YES
24-MAY-2011 23:51:10 YES
25-MAY-2011 01:43:56 YES
25-MAY-2011 02:39:11 YES
25-MAY-2011 03:10:26 YES
25-MAY-2011 04:15:15 YES
25-MAY-2011 04:23:26 YES
28-MAY-2011 03:58:02 YES
28-MAY-2011 03:58:11 YES
28-MAY-2011 03:58:11 YES
28-MAY-2011 03:58:12 YES
28-MAY-2011 03:58:13 YES
28-MAY-2011 04:00:16 YES
28-MAY-2011 04:04:24 YES
28-MAY-2011 04:04:27 YES
28-MAY-2011 04:04:33 YES
28-MAY-2011 04:04:36 YES

View 2 Replies View Related

SQL & PL/SQL :: Calculate Materialized View Refresh Time

Dec 11, 2012

i want to find out the last refresh elasped time for materialized view. i do not see last refresh elapsed time in data dictionary. i see only last refresh date in data dictionary.

how to find the last refresh elapsed time for materialized view.

View 3 Replies View Related

SQL & PL/SQL :: Calculate Time - Machine Has Worked Hourly Wise

Apr 16, 2010

I have a table with a date time column which actually stores the data as and when it is inserted. I need to calculate the time the machine has worked hourly wise. For example the table contains records from 13:00 to 13:15 and 13:45 to 14:00 i should get 30 mins as working time..

View 2 Replies View Related

PL/SQL :: How To Get Time Difference Between Two Dates

Oct 3, 2012

i am using this query to get the time difference between two dates.

select to_timestamp('2012-10-03 12:00:00','YYYY-MM-DD HH:mi:ss') - to_timestamp('2012-10-03 11:00:00','YYYY-MM-DD HH:mi:ss') as diff from dual;

but not getting the correct result.

View 26 Replies View Related

SQL & PL/SQL :: Query To Find The Time Difference

Aug 21, 2010

I need to query a table to get the time difference based on different dates.

SAMPLE DATA:

JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12

in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as

01:23:12-00:56:00 and the difference should be in minutes.

for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.

View 2 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

Forms :: New Form For Oracle Apps Turning Fields In Other Forms Black

Sep 23, 2011

I created a new form for Oracle Apps, At first when I ran the form from the application all the fields backgrounds were black, so I changed the background in the property palette to white and foreground to black.

Now it shows fine but when I close this form and open another those fields are now blacked out. What should I do, I know the problem comes from the new form.

View 5 Replies View Related

Forms :: How To Calculate Average In 6i

Apr 5, 2011

How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .

View 2 Replies View Related

Forms :: Calculate Sum In A Form?

Nov 23, 2007

how do w calculate sum in a form? for example in datablock sale, I have few rows of data, i want to calculate the total of product purchased (column totitem) and , sum of the customer have to pay. (totalprice). how to do the calculation in a program unit?

View 13 Replies View Related

Forms :: Data Source For Fields?

Mar 15, 2012

I have a horrible problem with EBS (actually, all problems with EBS are horrible) and I think I am stuck because of my ignorance of Forms. if I use terms that are not correct in the Forms world. The form consists of a number of named "blocks" and each block consists of a number of named "fields", not all of which are visible. I need to find the source of the data values in one of these fields. I have searched every table for a column of that name, also all the views and stored PL/SQL that I think might be relevant, but I can no find no mention of a column or variable with the same name as the field. The name does not get a hit in the online EBS tech ref manual, and only two ancient and irrelevant hits in MOS.

My question is: What are the possible sources of data for a field in a form? Have I missed any?

View 14 Replies View Related

Forms :: Search For NULL Fields

Feb 1, 2012

Is there any way to see items in the block having null in them? I want to figure out null field before inserting the data in the database table. At the moment I am having an error ORA 01400 which means that a NULL cannot be inserted into NOT NULL column. So, I want to create a procedure which will fire before insert trigger of the block and it will show me the names of items having null it them.

Procedure check_data(block_name in varchar2)
First_item varchar(20);
Current_item varchar(20);
Last_item varchar(20);
Begin

[Code]..

The problem is that I don't know the function to get the value inserted by user in the field so that I can compare it in IF condition.

View 3 Replies View Related

Forms :: Disable Fields Using Personalization

Oct 2, 2010

I am working on Oracle Apps 11i. Need Forms Personalization.

Client requirement: Selected user should only see Office Phone Number for all the people in the organization when he enters the Phones form.

Means, the client is having around 10 Lookup values for Phones (Like Mobile (Office), Mobile (Personal), Office (fax), Office No. etc). Now, they want to give access to admin, only to update the Phone numbers related to Office. So, when the admin logs-in to the phone form of a employee, that admin should not be able to see other phone numbers of that Employee.

Is it possible using Forms Personalisation?

View 4 Replies View Related

Forms :: How To Use Some Fields Of Form In Arabic

Sep 30, 2012

i want to use Arabic language in one of the fields available on the form.

i alreade have done the following

1. On database

update PROPS$
set value$ ='AR8MSWIN1256'
where name = 'NLS_CHARACTERSET';

2. On system registry

Have changed all nls_lang variable in the registry to 'American_America.AR8MSWIN1256'

3. On Form

Have changed the field font to 'Simplified Arabic' and reading order 'right to left'

4. On Operating System

Change the region and language setting
format 'Arabic (saudi Arabia)'
keyboard and languages add 'Arabic (saudi arabia)'

5. have restarted the database on the server and also the client machine.

View 13 Replies View Related

Forms :: All Block Fields Are Disabled

Dec 11, 2011

I am using Forms 6i in Application. In Form, After i enter the data and saved then,if i retrieve the data by using Ctrl+F11, all the fields of data block turns to be disabled (insert not allowed, update not allowed) of course i am getting the data.If i close the application, and open freshly it works good (insert allowed, update allowed).

View 1 Replies View Related

Forms :: How To Validate Primary Key Fields In 6i

Mar 5, 2011

i have a emp block with multiple rows in which col1,col2,col3,col4,col5,col6 are the columns...In emp block col1,col2,col3 are the primary key fields.. i need to validate the primary key columns such that it should not allow duplicates values in the primary key col's with out checking the database values with out using enforce primary key property

View 2 Replies View Related

Forms :: Choose Fields From Left

Feb 16, 2013

Is there any possibility of creating a selection interface similar to our report or forms wizard where user can select single fields or multiple fields together and when he clicks on the arrow those fields data to be inserted into another table.Please refer attachment.

--this is the main table
create table batch_item (batch_no varchar2(12),item_batch varchar2(12),total_batch_qty number);

insert into batch_item ('0001','a',300);
insert into batch_item ('0002','b',200);
insert into batch_item ('0003','a',102);

--after inserting the data i should be able to move this data to corresponding batches by manually selection and pushing them to a batch.create table ct_item (item varchar2(12),item_nm varchar2(20),item_qty number);

insert into om_item values ('a','alpha',2);
insert into om_item values ('b','beta',3);
insert into om_item values ('c','gama',4);

--left side is om_item and right will have batch items where batch qty will be accumulated upon choose arrows.

View 3 Replies View Related

Forms :: Fields Not Writing To Database Table

Jul 27, 2010

I have an Oracle Form 6i. There are two blocks. One is a database block called CUSTOMER and the other is a non-database block called CONTROL.

In the PRE-INSERT trigger of the database block, values from the non-database table block are passed to the database table block. When I pass values I use the :BLOCK_NAME.field_name eg. :CUSTOMER.scale_code := :CONTROL.scale.

In this form the values passed to the database block from the non-database block in the PRE-INSERT trigger do not use the block name e.g. :-

:warehouse := :global.default_warehouse;
:capturer :=captured
:scale_code := scale;
:date_captured := sysdate;
insert into dd_audit(cus_id,cap_date) values(:CONTROL.cus_id,SYSDATE);

This application used to work fine for months, last week when writing the values in the PRE-INSERT trigger, just the warehouse field had a value. The remaining fields after the warehouse did not pass any values, although it was verified that values would have been present in the non-database block. The date_captured field should have at least had the system date. The last insert into dd_audit was successful.

I have done numerous tests on our test database and could not replicate the problem. Would passing the values from the non-database block to the database block without the :BLOCK_name preceding the field name cause this problem.

View 4 Replies View Related







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