Looking for SQL to calculate days between two dates. Subtraction expression, date1 - date2, provides a fraction. How can I get whole days? Such as, 9/25/7 - 9/24/7 = 1, not .34949 based on hours in the day. Thank you.
-
-
kindly post your query for my reference. -
1 select SCHEDULED_PAY_d T,sysdate,SCHED ULED_PAY_dT-SYSDATE
2* from PS_PYMNT_VCHR_X REF where voucher_id ='00161896' and business_unit=' 10607'
SQL> /
SCHEDULED SYSDATE SCHEDULED_PAY_D T-SYSDATE
--------- --------- ------------------------
25-SEP-07 24-SEP-07 .343472222Comment
-
is the date field also storing time ?Comment
-
yes, the other field is storing a date.
I've come up with a possible solution to my own question. By converting the dates to Julian values and subtracting them it appears to provide me with the result that I'm looking for. I'm going to test this afternoon, post noon EST, to ensure the calculation doesn't include time of day. I don't think it will. Here's the code I believe will provide me with what I'm looking for:
select to_char(SCHEDUL ED_PAY_dT,'j')-to_char(sysdate ,'j').
Thank you for your replies.Comment
-
Mission accomplished, I've got it on my own. It works. Thank you for the reply.Comment
Comment