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
hassan08
Messages: 115
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 messageGo to next message
Littlefoot
Messages: 21403
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 #668095 is a reply to message #668094] Tue, 06 February 2018 03:14 Go to previous messageGo to next message
hassan08
Messages: 115
Registered: June 2011
Location: egypt
Senior Member
thank you the your code working perfect
select sum(attend_end - attend_start) * 24 hours
  2  from attend where emp_code = 1;

     HOURS
----------
95.1166667

but when try to execute this part from the code to get same you result not working to me
HH_MI
-------------------------------------------------
95:07
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;
Re: difference between date [message #668096 is a reply to message #668095] Tue, 06 February 2018 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13086
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 #668097 is a reply to message #668095] Tue, 06 February 2018 03:38 Go to previous messageGo to next message
Littlefoot
Messages: 21403
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One reason is that syntax is wrong (consult documentation about ROUND and LPAD functions. Pay attention to parenthesis).

Once you fix it, another one is aggregation (SUM) which requires GROUP BY clause.
Re: difference between date [message #668098 is a reply to message #668095] Tue, 06 February 2018 03:45 Go to previous message
John Watson
Messages: 7366
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

Previous Topic: recursive query
Next Topic: schedule job for multi threading
Goto Forum:
  


Current Time: Thu Apr 26 21:40:46 CDT 2018

Total time taken to generate the page: 0.01763 seconds