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


ADVERTISEMENT

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

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

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

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

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

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

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

Select All Dates Between Two Dates

Oct 22, 2003

How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

12/2/2003
12/3/2003
12/4/2003
12/5/2003

Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.

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

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

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

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

Application Net Connection Is Taking 30 To 40 Minutes

Jul 20, 2012

In one of my application oracle net connection is taking 30 to 40 ms(tnsping servicename),but in actual connection is always taking more than 45 ms, and my application requirement is below than 10 to 20 ms.

And can i use any other connection method like hostname, ezconnect etc.

View 7 Replies View Related

Query On V$session_longops Taking Over 5 Minutes

Jan 18, 2013

I am working with a new client and am still waiting on access to systems so I'm a bit hampered in looking at details of the database and environment. The database is OLTP and typical response time for returning result sets is under 2 seconds, often less than 1 second.

A simple SELECT on a few columns of v$session_longops (no subqueries, group by, having, etc) ran for more than 5 minutes.

View 3 Replies View Related

Can Create Range Partition Of Like 10 Minutes?

Oct 18, 2012

I want to ask whether I can create range partition of like 10 minutes? Like I have data of 1 hour and I want to make 10 partition?

View 12 Replies View Related

DB 11g On Min2K3 Take 100% Of One Core 2 To 4 Minutes After Startup

Feb 23, 2013

I have a Windows 2003 VirtualBox instance, to which I assigned 3 out of 4 cores my laptop has. This is a demonstration environment for an Oracle vertical product. I got it from my colleagues. The OS boots without starting the DB services - I did this deliberatley while trying to figure out what is happening.

About 2 and half to 3 and a half minutes after the service is started the oracle.exe "latches onto a core and does not let go" (as best as I can describe what I see). With 3 cores I see 33%-34% cprocessor use in the task manager with oracle.exe doing all the using. Nothing else is started. There is no process of which I am aware which actually uses the DB. I only start the TNS listener and the database service.

Once I start it, the demonstration software uses the database extensively for complex queries. With one of the 3 cores 100% used by the oracle.exe I am running short on CPU at times, which makes the demonstratin seem slaggish, and queries take longer than is really acceptable (not surprising seeing oracle.exe is very busy doing I know not what).

View 2 Replies View Related

PL/SQL :: How To Convert Minutes To Hour And Minute

Oct 4, 2012

i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.

can i get in this format?

View 12 Replies View Related

LGWR Switch In Database Alert Log Every 3-4 Minutes

Jul 6, 2012

i am seeing (LGWR switch) happening in my databsae alert log every 3-4 minutes. is that appropriate? if not, what measure can i take to reduce this?

View 1 Replies View Related







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