## 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?

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 …..

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

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

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
• 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.
report
• 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.
```/Free