Number of days between 2 dates

20 pts.
Tags:
AS/400 Query
How do I find the number of days between 2 dates? Julian or MM/DD/YY?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Pherrick ,

The answer to your question is quiet simple. I will implement this by writing a function wherein you could pass the two desired days …..

if object_id(‘fBusinessDays’) Is not null
drop function fbusinessdays
go
create function fBusinessDays (@start datetime, @end datetime)
returns int
as
begin
/*
Description:
Function designed to calculate the number of business days
between two dates.
*/
declare
@wks int
,@days int
,@sdays int
,@edays int

– Find the number of weeks between the dates. Subtract 1
– since we do not want to count the current week.
select @wks = datediff( week, @start, @end) – 1
– calculate the number of days in these full wks.
select @days = @wks * 5
– Get the number of days in the week of the start date. This is the days
– between Saturday (datepart=7) and the startdate. We also remove the
– Sunday (datepart=1). If the first day is a Saturday, do not exclude
– this twice.
if datepart( dw, @start) = 7
select @sdays = 7 – datepart( dw, @start)
else
select @sdays = 7 – datepart( dw, @start) – 1
– Calculate the days in the last week. These are not included in the
– week calculation. Since we are starting with the end date, we only
– remove the Sunday (datepart=1) from the number of days. If the end
– date is Saturday, correct for this.
if datepart( dw, @end) = 7
select @edays = datepart( dw, @end) – 2
else
select @edays = datepart( dw, @end) – 1

– Sum everything together.
select @days = @days + @sdays + @edays
return( @days)
end

Now if you were to put in a start date greater than the end date you would end up geting a -ve value…If you do not require that then just check for -ve values and pass a 0 or an error message …which ever you prefer……

Hope this would be of help to you…….AND dont forget to rate this!!!!!!!!!!!!!!!!
——————————————————————————————————————————————–
Hey Pherrick,

I’m a QRY user, i’ll admit it, but only when I need to look at something quick. Now, if you want to get the difference between 2 dates, and you’re thinking one day, hey, I wish i knew how to do this in RPGLE or RPGLE Free-Form, then look no further…

Let me educate you in the New-School type of Date manipulation code…
Add the following code into a Test RPGLE source member. This can even be done via Free-Form, just remember to put the semi-colon after each statement!!!

Here ya go:
<b>*============================================================================*</b>
d DiffInDays s 3s 0 inz

d TodayInISO s d inz
d FromISO s d inz

d TodayInJUL s d inz datfmt(*JUL)
d FromJUL s d inz datfmt(*JUL)

d TodayInMDY s d inz datfmt(*MDY)
d FromMDY s d inz datfmt(*MDY)
<b> *————————————————————————————————————————————*
* HERE’S HOW TO DO IT IN *ISO</b>
c eval TodayInISO = %Date() TODAYINISO = ’2008-05-28′
c eval FromISO = TodayInISO – %MONTHS(6) FROMISO = ’2007-11-28
c eval DiffInDays = %Diff(TodayInISO:FromISO:*days) DIFFINDAYS = 182

<b> * HERE’S HOW TO DO IT IN *JUL</b>
c eval TodayInJUL = %Date() TODAYINJUL = ’08/149′
c eval FromJUL = TodayInJUL – %MONTHS(6) FROMJUL = ’07/332′
c eval DiffInDays = %Diff(TodayInJUL:FromJUL:*days) DIFFINDAYS = 182

<b> * HERE’S HOW TO DO IT IN *MDY </b>
c eval TodayInMDY = %Date() TODAYINMDY = ’05/28/08′
c eval FromMDY = TodayInMDY – %MONTHS(6) FROMMDY = ’11/28/07′
c eval DiffInDays = %Diff(TodayInMDY:FromMDY:*days) DIFFINDAYS = 182
<b> *————————————————————————————————————————————*</b>
c eval *inlr = *on
<b> *============================================================================*
</b>

Hey, it ain’t QRY, but this will definately help if you need to do this. I have a WORD document showing how to do all types of date manipulations. If you want a copy of it, just seek out The Shattered Raven.

~SR

============================================================

Query/400 is many years too old and has been effectively obsolete for a decade or more. Query Manager should be used instead. A QM query makes use of SQL functions and date arithmetic is easy.

Your query might SELECT DAYS(ShipDate) – DAYS(OrderDate) in order to find the number of days it took from the date an order was received to the date it was shipped.

Tom

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Cwc
    In what language are you wanting to do this? If you're using RPG (IV / ILE), you could do this quite easily with just a few lines of code (let me know if you want an example). I believe COBOL ILE has some date functions as well, although it would take more lines of code.
    4,290 pointsBadges:
    report
  • TSCHMITZ
    Hi, you'll find a complete tutorial with example at http://systeminetwork.com/article/calculate-years-months-and-days-between-two-days, from Bob Cozzy.
    470 pointsBadges:
    report
  • R0
    Instead of using such a complicetd Function just use a predefine function SUBDUR which will calculate the difference between the two days C DATE3 SUBDUR DATE4 NUM_DAYS:*D 8 0
    85 pointsBadges:
    report
  • Cwc
    ^I recommend not using SUBDUR and ADDDUR, as they're somewhat deprecated operations, since the built in function (BIF) %Diff( ) is more useful and is compatible with free format RPG. Using fixed format RPG is obsolete. To elaborate further on the previous examples, here's another one in free format. Assume that the FromDate and ToDate are decimal variables with a length of 8.
    /Free
     NbrOfDays = %Diff( %Date(FromDate): %Date(ToDate): *Days);
    /End-Free
    
    4,290 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following