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
create function fBusinessDays (@start datetime, @end datetime)
Function designed to calculate the number of business days
between two dates.
– 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)
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
select @edays = datepart( dw, @end) – 1
– Sum everything together.
select @days = @days + @sdays + @edays
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!!!!!!!!!!!!!!!!
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:
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)
* 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
c eval *inlr = *on
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.
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.