| Difference of two date field [message #79208] |
Wed, 15 May 2002 03:24  |
Sanjay Singh
Messages: 4
Registered: May 2002
|
Junior Member |
|
|
Hi Friends,
Pls help me about ...
How can I get the difference between two date (divided in two fields, date part and time parts) field in terms of hours, minutes and seconds.
looking for ur supports..
Bye
|
|
|
|
|
|
| Re: Difference of two date field [message #79211 is a reply to message #79208] |
Wed, 15 May 2002 04:54  |
Remash
Messages: 52
Registered: November 2000
|
Member |
|
|
If you are looking for the time elapsed between two dates,
then consider the following coding. Assume that DATE1 and DATE2 and two date-time fields
in a form and DATE2 greater than DATE1.
Declare
J1 Number;
J2 Number;
S1 Number;
S2 Number;
DI Number;
H Number;
M Number;
D Number;
S NUMBER;
SE1 Number;
SE2 Number;
Begin
-- Convert the dates into Julian. (format 'J') days since 31-DEC-4713 and
-- time into seconds since midnight (format 'SSSSS')
J1 := To_Number(To_Char(:DATE1,'J'));
J2 := To_Number(To_Char(:DATE2,'J'));
S1 := To_Number(To_Char(:DATE1,'SSSSS'));
S2 := To_Number(To_Char(:DATE2,'SSSSS'));
-- Convert the days into seconds (multiply 24 days X 60 minute X 60 seconds)
-- and add to the seconds since midnight
SE1 := (J1*24*60*60)+S1;
SE2 := (J2*24*60*60)+S2;
-- Find the difference (in seconds)
DI := SE2-SE1;
-- Convert the diff. (in seconds) into no. of days
D := Floor(DI/24/(60*60));
-- Deduct the no. of days (in seconds) from the diff. and find the hours
H := Floor((DI - (D*24*60*60)) / (60*60));
-- Deduct the days and hours and find the minutes
M := Floor((DI - (D*24*60*60) - (H*60*60))/60);
-- Deduct the days, hours and minutes and find the seconds
S := DI - (D*60*60*24)-(H*60*60) - (M*60);
EXCEPTION
WHEN OTHERS THEN
-- return proper error message
return;
End;
|
|
|
|
| Re: Difference of two date field [message #81343 is a reply to message #79211] |
Thu, 06 February 2003 22:57 |
Vikram Nagaraj
Messages: 1
Registered: February 2003
|
Junior Member |
|
|
wrote a function for the same,
/* This will return number of seconds between in_Date and d_std_Date*/
Create or Replace function prc_secsSince(in_Date in Varchar2)
RETURN Number
IS
d_cur_Date Date;
d_std_Date Date ;
noDays_cur_Date Number;
noDays_std_Date Number;
noSecs_std_Date Number;
noSecs_cur_Date Number;
tot_secs_cur_Date Number;
tot_secs_std_Date Number;
diff_secs Number;
Begin
d_std_Date := to_date('15-AUG-1947','DD-MON-YYYY');
d_cur_Date := to_date(in_Date,'YYYY-MM-DD HH24:MI:SS');
-- Convert the dates into Julian. (format 'J') days since 31-DEC-4713
noDays_cur_Date := to_Number(to_Char(d_cur_Date,'J'));
noDays_std_Date := to_Number(to_Char(d_std_Date,'J'));
-- Seconds since midnight (format 'SSSSS')
noSecs_cur_Date := to_Number(To_Char(d_cur_Date ,'SSSSS'));
noSecs_std_Date := to_Number(To_Char(d_std_Date ,'SSSSS'));
-- Convert the days into seconds (multiply 24 days X 60 minute X 60 seconds)
-- and add to the seconds since midnight
tot_secs_cur_Date := (noDays_cur_Date*24*60*60)+noSecs_cur_Date;
tot_secs_std_Date := (noDays_std_Date*24*60*60)+noSecs_std_Date;
-- Find the difference (in seconds)
diff_secs := tot_secs_cur_Date-tot_secs_std_Date;
dbms_output.put_line('Dif is ' || diff_secs );
Return(diff_secs);
Exception
When others then
dbms_output.put_line(sqlerrm);
End prc_secsSince;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
