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


ADVERTISEMENT

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

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

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

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

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

JDeveloper, Java & XML :: Date Time Omits Time Part

Jun 4, 2012

When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.

If I try the same in SQLplus with to_date it works however fails in PL/SQL.

XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......

View 3 Replies View Related

How To Set Interval Time 4hrs In Dbms_jobs But Starting Time 3:00 Am

Jul 25, 2013

how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.

i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..

View 2 Replies View Related

Fetch Time Greater Than Execute Time?

Dec 27, 2010

I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.

What does this exec and Fetch time is?

How to improve fetch time, (esp with sub-query) ?

View 2 Replies View Related

SQL & PL/SQL :: Get End Time By Query Between Start Time And Duration

Jun 16, 2013

I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code

""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......

View 5 Replies View Related

PL/SQL :: How To Check If Time Is Closer To Start Or End Time

Jan 31, 2013

I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.

This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.

The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.

View 5 Replies View Related

SQL & PL/SQL :: Oracle - Compare End-time With The Start-time?

Jun 7, 2010

. I have this query:

select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1

[code]...

As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?

View 6 Replies View Related

SQL & PL/SQL :: How To Find Time Offset For Given Time

Sep 23, 2010

My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?

View 1 Replies View Related

SQL & PL/SQL :: Database To Show Time As OS Time

Jul 4, 2011

I have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.

View 4 Replies View Related

Get All Dates Between 2 Dates

Aug 5, 2010

I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example
suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.

something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.

View 2 Replies View Related

SQL & PL/SQL :: Dates Between 2 Dates

Nov 9, 2010

I want the dates between 2 dates. Suppose i give the dates 01-jan-2010 and 31-jan-2010 and i need the following output.

01-jan-2010
02-jan-2010
03-jan-2010
.
.
.
.
31-jan-2010

View 11 Replies View Related







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