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..
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..
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies View RelatedDetail 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?
i have two dates like
30-may-2011 11:50:34 and 27-may-2011 13:59:37
how i can calculate proper hrs between these dates? My condition is time calculate from 8.30AM to 5.30PM
it does not considered 24 hrs
if i calculate hrs
30-may-2011 11:50:34 - 27-may-2011 13:59:37
then it shows 69.85 hrs . but it considered full 24 hrs.
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
select to_date('25-04-2012', 'DD-MM-YYYY') + rownum -1 dt
from dual
connect by level <= to_date('05-05-2012', 'DD-MM-YYYY') - to_date('25-04-2012', 'DD-MM-YYYY') + 1;
The above query returning the following output,
DT
04/25/2012
04/26/2012
04/27/2012
04/28/2012
04/29/2012
04/30/2012
05/01/2012
05/02/2012
05/03/2012
05/04/2012
05/05/2012
here I need to exclude the Dates which comes on 'saturday' and 'sunday' and also the common holiday..Here it is '01-May-2012' and I need the output like the following,
DT
04/25/2012
04/26/2012
04/27/2012
04/30/2012
05/02/2012
05/03/2012
05/04/2012
I need the common query to calculate between any two dates.
I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00 (and this is my problem, how to add this condition in this function)
View 4 Replies View Relatedhow to caluclate days between two dates of single timestamp filed and with this
query
Select * from m_activity_transaction where actn_opp_id in (
Select actn_opp_id from m_activity_transaction where ACTN_ACTV_ID = 218
Group by actn_opp_id
[code]...
and i nedd to caluclate no.of days between two dates like 27-JAN-12 11.06.20.000000 AM and 08-FEB-12 05.32.54.000000 PM where actn_id is unique AND ACTN_OPP_ID IS NOT UNIQUE.
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 RelatedI 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
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]....
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
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
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.
I am looking to subtract two columns and get the difference.
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
for the below table
create table RM_TR_INVESTMENT
(
AS_ON_DT DATE not null,
EXP_ID NUMBER(10) not null,
BO_REF_ID VARCHAR2(30),
FO_REF_ID VARCHAR2(30),
BK_PRODUCT_ID VARCHAR2(20),
BK_INV_TYPE_ID VARCHAR2(20),
[code]....
I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?
I am trying to find difference between two dates .I am getting 73045 as the difference. I want 12 as difference. find the below lines of
request_date:=TO_DATE('1-jul-2011','dd-mon-yyyy');
Today_date:=TO_DATE(sysdate,'dd-mon-yyyy');
dbms_output.put_line('>> difference is ' ||( Today_date-requestdate) || ' days' );
Output:-difference is 73045 days
( if tried '2-jul-2011' as request_date, i am getting 73046.
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 ?
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.
I am running some SQL to try to find how long, in hours:mins:seconds a concurrent request in Oracle EBS takes to run.
I have a basic start here:
SELECT fcr.actual_start_date start_
, fcr.actual_completion_date end_
, fcr.actual_completion_date - fcr.actual_start_date diff_1
-- , TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_
FROM applsys.fnd_concurrent_requests fcr
[code].......
I'd really like to get the difference expressed in hours, minutes and seconds. I tried:
, TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_But that doesn't work - I get an ORA-01722: invalid number error.
I also tried:
, TO_CHAR(fcr.actual_completion_date - fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') diff_But got an ORA-01481: invalid number format model error
In my server , already 10g r2 is installated , now am installaling 11r2, during this, at final stage while running root.sh , it will propmt to override 3 files, oraenv,dbhome .. etc under /usr/local/bin in solaris sys, as these files are already owned by 10g owner ,what i have to select (y or n)?
what if i select y? it will override three files
what if i select n? default option
i'm trying to get the difference between two dates so i have two date itemsthen i did daynamic action (set Value) and choose pl/sql function then added the following codeDecalrestart_date date;end_date datebeginstart_date:=to_date(:p20_start_date,'dd/mm/rrrr');select to_date (sysdate,' dd/ mm /rrrr')into end_datefrom dual;return end_date-start_date;end; but when i run the page i got error ora-01861
View 1 Replies View RelatedWhat is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View Relatedhow can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation
how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days
PMT function which calculates the payment for a loan based on constant payments and a constant interest rate. I need to build PMT function in oracle.
View 2 Replies View RelatedI created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
how do i can make oracle function for the following vb6 function.
This function is used for calculating working days between two date.
****************************************
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
[code]......
I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).
What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.
I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.
I cannot see how to convert a STDDEV result into being a "number of standard deviations".
(If I have not expressed myself clearly, I'm using this sort of stuff URL....and I'm trying to find >3-sigma records)
simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:
Mean = 7.6 seconds (from my sample data mentioned in the above post)
StdDev = 4.29 seconds
So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?
So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?
I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?
Do we have analytic function equivalent of following?
select object_type,owner from dba_objects group by object_type,owner ;
I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner
OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................
also how can I get the SRNO?
I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record
I don't want to wrap this select inside other select
