OraFAQ Forum: SQL & PL/SQL » difference between date

Home » SQL & PL/SQL » SQL & PL/SQL » difference between date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

difference between date [message #668093]

Mon, 05 February 2018 14:41

hassan08
Messages: 122
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

Report message to a moderator

Re: difference between date [message #668094 is a reply to message #668093]

Mon, 05 February 2018 14:59

Littlefoot
Messages: 21783
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?

Report message to a moderator

Re: difference between date [message #668095 is a reply to message #668094]

Tue, 06 February 2018 03:14

hassan08
Messages: 122
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;

Report message to a moderator

Re: difference between date [message #668096 is a reply to message #668095]

Tue, 06 February 2018 03:37

cookiemonster
Messages: 13904
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?

Report message to a moderator

Re: difference between date [message #668097 is a reply to message #668095]

Tue, 06 February 2018 03:38

Littlefoot
Messages: 21783
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.

Report message to a moderator

Re: difference between date [message #668098 is a reply to message #668095]

Tue, 06 February 2018 03:45

John Watson
Messages: 8880
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 30 00:17:05 CDT 2023