Get Date Difference In Days Between Two Dates In Reports?

Sep 11, 2010

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

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Finding Individual Dates Having Date Plus 2 Days

Jan 26, 2011

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10

[Code]...

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

View 2 Replies View Related

SQL & PL/SQL :: Days Difference - Start And End Date

Jul 7, 2012

Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00

How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color

query out put should give as below

start_date + next record ,end_date - Previous records should give the difference of
6
1

View 12 Replies View Related

Finding Individual Dates Having Dates Plus 2 Days

Jan 26, 2011

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10
1220.01.10
1321.01.10

1423.01.10

1526.01.10
1627.01.10

1729.01.10
1831.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

View 2 Replies View Related

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

Sep 2, 2011

I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).

View 7 Replies View Related

SQL & PL/SQL :: Days Between Dates

Mar 14, 2012

I have not defined the table ( I only have privileges to query data).

I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).

Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).

I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)

clientid / cycleno / visdt
---------------------------
1200004 / 1 / 10OCT2011
1200004 / 2 / 31OCT2011
1200004 / 3 / 21NOV2011
1200004 / 4 / 05DEC2011
1200004 / 5 / 03JAN2012
...
1000005 / 1 / 04NOV2011
1000005 / 2 / 03DEC2011
1200004 / 1 / 10JAN2012
1200004 / 2 / 15FEB2012
.
.
.

The code below is the only one that kind of seemed to work, but it is definitely not giving me the right results.

SELECT cycleno1, visdt1, cycleno2, visdt2, to_date(visdt1) - to_date(visdt2) days

FROM (SELECT clientid, cycleno cycleno1, visdt visdt1,
LEAD (visdt, 1) OVER (ORDER BY cycleno) visdt2
FROM CYCLE) a

[Code]....

I am getting a mess of a result of the kind:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 1/ 18OCT2011 / -8
1 / 10OCT2011 / 2/ 18OCT2011 / -8
1 / 10OCT2011 / 3/ 18OCT2011 / -8
1 / 10OCT2011 / 4/ 18OCT2011 / -8
1 / 10OCT2011 / 5/ 18OCT2011 / -8

I need my result to look like:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 2/ 31OCT2011 / 21
2 / 31OCT2011 / 3/ 21NOV2011 / 22
3 / 21NOV2011 / 4/ 05DEC2011 / 15
4 / 05DEC2011 / 5/ 03JAN2012 / 30
5 / 03JAN2012 / / /
.
.
.

View 3 Replies View Related

Number Of Days Between Dates?

Jan 14, 2008

What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?

View 1 Replies View Related

SQL & PL/SQL :: Month Wise Days For Between Given Dates?

Apr 28, 2010

I have a table with starting date and ending date.

I want to fetch monthwise days for given two dates

IE starting 25/12/2009 ending 25/03/2010

Result should like below.

Dec-09 7
Jan-10 31
Feb-10 28
Mar-10 25

I have tried this but it is not giving me the result which want..

select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from
(SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366

[code].....

View 3 Replies View Related

SQL & PL/SQL :: How Many Days Thursday And Friday Between Two Dates

Nov 13, 2011

I want function return me how many days Thursday and Friday between two dates.

View 1 Replies View Related

SQL & PL/SQL :: Show Number Of Days Between Two Dates?

Apr 18, 2013

i want to create function to show number of days between to days such as number of Friday days between to dates

View 13 Replies View Related

PL/SQL :: Oracle Dates And Working Days

Sep 5, 2012

Is there an Oracle date function that ignores public bank holidays and calculates working days only?

View 4 Replies View Related

PL/SQL :: How To Calculate Days Between Two Dates Of One Timestamps Field

Sep 25, 2012

how 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.

View 6 Replies View Related

SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?

Mar 10, 2011

Split a date into new dates according to black out dates!

Here is my tables:

CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;

[code]....

I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.

Note: Travel Date can be between 0000-01-01 - 9999 12 31

Sample:

Travel Date:

Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11

Black Out Dates:

BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29

Excepted Result:

New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11

Visually:

Travel Date : -----[--------------------------]--

A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------

Result :

X1 : -----------[--]--------------------
X2 : -----------------------[--------]--

Sample 2:

Travel Date : -[--------------------------------]--

BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------

Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--

How can I do it using PL SQL ?

View 5 Replies View Related

Function To Find Business Days Between Two Dates - Query Tuning

Mar 17, 2011

I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.

The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:

select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL))
from dwt_dvt_work_time where dwt_create_date > sysdate - 30
and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id

Here's the function:
CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE)
RETURN NUMBER
IS total_days NUMBER(11,2);
holiday_days NUMBER(11,2);
[code]....

View 1 Replies View Related

SQL & PL/SQL :: Complex Days Computation / Multiple Number Of Same Transaction Dates

May 22, 2010

The data in one of the temporary table has been compiled as below. Number of Days elapsed between two transaction dates is required to be computed and multiplied with the balance. There can be multiple number of same transaction dates. When previous date and the current transaction dates are same the resulting number of day difference should be 0. But when they are different the difference between them is to be computed. On the last day of the Financial Year i.e. '31/03' of any year the difference day should be shown as 1 so as to make 365 or 366 days in a year. Simply deducting d1 from d2 on 31st will not be suffice as the difference is one day less.

----------------------------------------------------------------------------------------- -------
Transaction Narration DebitCreditBalanceNo of DaysProduct
DateDifference
----------------------------------------------------------------------------------------- --------
01/04/2009Opening Balance 45020450277346654
17/06/2009ByAmt5044521253424
29/06/2009By Amt1004352156678912
02/12/2009By Amt424310119476000
[code]....

improve the above code and get the desired output result.

View 5 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 :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

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

Manually Find Difference In Days And Multiply By 24

Apr 25, 2011

1) First Problem:

Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?

SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....

1) Second Problem:

For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.

SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Dates

Jan 23, 2013

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

View 9 Replies View Related

PL/SQL :: Difference Between 2 Dates

Sep 24, 2012

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 ?

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Two Dates - Unexpected Value

Jul 13, 2011

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.

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

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

PL/SQL :: Finding Difference Between Two Dates In Hh:mm:ss Format

Sep 20, 2012

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

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

Application Express :: Difference Between Two Dates Error

Aug 3, 2013

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 Related

PL/SQL :: Date Difference - Include Both Start And End Date In Range

Aug 8, 2013

select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013')  FROM DUAL 

gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?

View 3 Replies View Related

Display Data From Current Date To Next 15 Days?

Jan 24, 2007

I have a Employee data as below with EID and DOB

EID DOB
----------------------
1 03/01/1950
2 14/01/1950
3 27/01/1950
4 02/02/1950
5 04/03/1950
6 12/03/1950
7 28/03/1950
8 28/02/1951
9 08/03/1951
10 28/03/1951

I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.

View 8 Replies View Related







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