Date calculation excluding weekends
A 4GL developer asked how to count the number of days between two dates while excluding weekends. A working answer was posted: a stored procedure (workdays) that swaps the dates if needed, loops day by day from the earlier to the later date, and increments a counter only when WEEKDAY() returns 1-5, returning the business-day count; the poster confirmed he'd use it. Others noted the routine ignores public holidays and suggested adding a company-specific holidays table and subtracting matching dates - the asker agreed but no holiday-aware code was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, I would much appreciate if some guru advises me of some quick fix 4GL function that calculates the difference between 2 dates excluding the weekends. Eg: let work_days = date_end - date_start. If date_start is today, which is Thursday 04/09/2008, and date_end is Monday 08/09/2008 the work_days is 4. But excluding the weekends (Sartuday 06/08/2008 and Sunday 07/08/2008) then the actual work_days is 2 9and this is the number I want). Best regards, Long NGuyen Informix 4GL Programmer TTLABS system - ARCBS 153 Clarence street - Sydney Australia. Win a MacBook Air or iPod touch with Yahoo!7. http://au.docs.yahoo.com/homepageset
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday 06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
Thanks Gary,
Certainly will use it.
Long N
===========================
----- Original Message ----
From: GARY GU <gary_gu@engin.com.au>
To: ids@iiug.org
Sent: Thursday, 4 September, 2008 4:56:30 PM
Subject: Re: Date calculation excluding weekends [13279]
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday 06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Win a MacBook Air or iPod touch with Yahoo!7.
http://au.docs.yahoo.com/homepageset
And you work on Holidays!!!
-ScottM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
GARY GU
Sent: Thursday, September 04, 2008 12:57 AM
To: ids@iiug.org
Subject: Re: Date calculation excluding weekends [13279]
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL
function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is
Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday
06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the
number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Scott,
I noticed that as well. Since holidays vary from company to company,
this routine will need to incorporate a selection from a table (ie,
holidays) that lists each holiday and, if found, decrement the count by
1 for each date found in the holiday table.
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Main (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Scott MacKenzie
Sent: Thursday, September 04, 2008 10:09 AM
To: ids@iiug.org
Subject: RE: Date calculation excluding weekends [13284]
And you work on Holidays!!!
-ScottM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
GARY GU
Sent: Thursday, September 04, 2008 12:57 AM
To: ids@iiug.org
Subject: Re: Date calculation excluding weekends [13279]
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL
function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is
Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday
06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the
number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
No,
How to exclude holidays as well?
Thanks for reminding me holidays.
Long N
----- Original Message ----
From: Scott MacKenzie <scottm@dinecollege.edu>
To: ids@iiug.org
Sent: Friday, 5 September, 2008 1:09:16 AM
Subject: RE: Date calculation excluding weekends [13284]
And you work on Holidays!!!
-ScottM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
GARY GU
Sent: Thursday, September 04, 2008 12:57 AM
To: ids@iiug.org
Subject: Re: Date calculation excluding weekends [13279]
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL
function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is
Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday
06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the
number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Win a MacBook Air or iPod touch with Yahoo!7.
http://au.docs.yahoo.com/homepageset
You're right Clifton.
I have to create a holiday table as well.
Cheers,
Long N
============
----- Original Message ----
From: Clifton Bean <Clifton.Bean@ctm.com>
To: ids@iiug.org
Sent: Friday, 5 September, 2008 1:23:50 AM
Subject: RE: Date calculation excluding weekends [13285]
Scott,
I noticed that as well. Since holidays vary from company to company,
this routine will need to incorporate a selection from a table (ie,
holidays) that lists each holiday and, if found, decrement the count by
1 for each date found in the holiday table.
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Main (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Scott MacKenzie
Sent: Thursday, September 04, 2008 10:09 AM
To: ids@iiug.org
Subject: RE: Date calculation excluding weekends [13284]
And you work on Holidays!!!
-ScottM
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
GARY GU
Sent: Thursday, September 04, 2008 12:57 AM
To: ids@iiug.org
Subject: Re: Date calculation excluding weekends [13279]
May need a stored procedure to do this:
create procedure workdays(d1 DATE, d2 DATE)
returning int;
define ld1,ld2 DATE;
define n int;
if d1 > d2 then
let ld1 = d2;
let ld2 = d1;
else
let ld1 = d1;
let ld2 = d2;
end if;
let n = 0;
while ld1 < ld2
if weekday(ld1) between 1 and 5 then
let n = n+1;
end if;
let ld1 = ld1 +1;
end while;
return n;
end procedure;
select workdays(current, date('08/09/08'))
from systables
where tabid = 1
;
and returns 2.
Gary
--------------------------------------------
Hi all,
I would much appreciate if some guru advises me of some quick fix 4GL
function
that calculates the difference between 2 dates excluding the weekends.
Eg: let work_days = date_end - date_start.
If date_start is today, which is Thursday 04/09/2008, and date_end is
Monday
08/09/2008 the work_days is 4. But excluding the weekends (Sartuday
06/08/2008
and Sunday 07/08/2008) then the actual work_days is 2 9and this is the
number
I want).
Best regards,
Long NGuyen
Informix 4GL Programmer
TTLABS system - ARCBS
153 Clarence street - Sydney
Australia.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Win a MacBook Air or iPod touch with Yahoo!7.
http://au.docs.yahoo.com/homepageset