Home » SQL & PL/SQL » SQL & PL/SQL » difference between date
difference between date [message #668093] |
Mon, 05 February 2018 14:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/043a1274cb670fd010565969fc56603d?s=64&d=mm&r=g) |
hassan08
Messages: 123 Registered: June 2011 Location: egypt
|
Senior Member |
|
|
how can using sum with date and final result greater than 24 hours
i try this code but the result not right
select sum(attend_start-attend_end ) *60
from attend where emp_code = 1;
create table attend (emp_code number, attend_start date , attend_end date);
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/21/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/21/2018 23:00:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/22/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/22/2018 22:10:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/23/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/23/2018 21:00:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/24/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/24/2018 20:19:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/25/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/25/2018 19:08:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/28/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/28/2018 21:30:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/27/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/27/2018 23:00:30', 'MM/DD/YYYY HH24:MI:SS'));
insert into scott.attend
(emp_code, attend_start, attend_end)
values
(1, to_date('01/26/2018 09:00:30', 'MM/DD/YYYY HH24:MI:SS'), to_date('01/26/2018 17:00:30', 'MM/DD/YYYY HH24:MI:SS'));
commit;
and the result must be
83:07
|
|
|
Re: difference between date [message #668094 is a reply to message #668093] |
Mon, 05 February 2018 14:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
When you subtract two dates, the result is number of days. If you need to convert it to hours, you'd multiply it by 24 (not 60). But, for number of minutes, you'd multiply that result (number_of_days * 24) by 60.
Now, what does that "83:07" mean?
SQL> select sum(attend_end - attend_start) end_start,
2 sum(attend_start - attend_end) start_end
3 from attend where emp_code = 1;
END_START START_END
---------- ----------
3.96319444 -3.9631944
SQL>
Obviously, if you want to get a positive result, you should subtract smaller value (i.e. ATTEND_START) from a larger value (i.e. ATTEND_END), not vice versa.
3.96 days x 24 hours in a day make 95.12 hours:
SQL> select sum(attend_end - attend_start) * 24 hours
2 from attend where emp_code = 1;
HOURS
----------
95.1166667
SQL>
Or, if you prefer this:
SQL> with hours as
2 (select sum(attend_end - attend_start) * 24 hours
3 from attend where emp_code = 1
4 )
5 select trunc(hours) ||':'||
6 lpad(round((hours - trunc(hours)) * 60), 2, '0') hh_mi
7 from hours;
HH_MI
-------------------------------------------------
95:07
SQL>
Therefore, I'm not sure what the result you suggested "83:07" actually means. Could you explain it?
|
|
|
|
Re: difference between date [message #668096 is a reply to message #668095] |
Tue, 06 February 2018 03:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Not working is not a useful description. The error you're getting would be:
SQL> select sum(attend_end - attend_start) * 24 +
2 lpad(round((attend_end - attend_start) - trunc(attend_end - attend_start)*60,2,'0')
3 from attend where emp_code = 1;
select sum(attend_end - attend_start) * 24 +
lpad(round((attend_end - attend_start) - trunc(attend_end - attend_start)*60,2,'0')
from attend where emp_code = 1
ORA-00907: missing right parenthesis
SQL>
And that error means what it says - you haven't got the right number of parenthesis.
However - this isn't the code LF posted. What are you trying to achieve with this modification?
|
|
|
|
Re: difference between date [message #668098 is a reply to message #668095] |
Tue, 06 February 2018 03:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I think you have confused your data data types. Subtracting one date from another will give you a number. You can use ROUND and TRUNC on numbers, but not LPAD. LPAD needs a string, so you are forcing Oracle to convert the number to a string implicitly. Not a good idea.
[Updated on: Tue, 06 February 2018 03:46] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:51:54 CDT 2024
|