Sorry if this is not a totally DBISAM focused question, but it is pure DB & I am sure you
guys must have had to solve this one sometimes!

  Q.FieldByName('TotalDays').AsFloat := (Q.FieldByName('DateEnd').AsDateTime -
Q.FieldByName('DateStart').AsDateTime);

I am currently using this code snipet in Delphi to count the number of days between 2
dates. It works, though I have to check that both fields contain dates etc.

However now the customer (don't we all love them) wants a second field to contain the
week-day (i.e. count Monday - Friday, don't include Saturday & Sunday in the count) count
between 2 dates ...

i.e. 1st February 2006 - 15th February 2006 is 15 days, but only 11 if you just count
Monday - Friday.

--

Someone who works on a Work / Payroll system must have done this stuff! Is there an easy
way to get the number?

Adam

adam <adam@nospamplease.fmfoods.co.uk> wrote on Thu, 2 Mar 2006 07:52:15 -0500

>Sorry if this is not a totally DBISAM focused question, but it is pure DB & I am sure you
>guys must have had to solve this one sometimes!
>
> Q.FieldByName('TotalDays').AsFloat := (Q.FieldByName('DateEnd').AsDateTime -
>Q.FieldByName('DateStart').AsDateTime);
>
>I am currently using this code snipet in Delphi to count the number of days between 2
>dates. It works, though I have to check that both fields contain dates etc.
>
>However now the customer (don't we all love them) wants a second field to contain the
>week-day (i.e. count Monday - Friday, don't include Saturday & Sunday in the count) count
>between 2 dates ...
>
>i.e. 1st February 2006 - 15th February 2006 is 15 days, but only 11 if you just count
>Monday - Friday.
>
>--
>
>Someone who works on a Work / Payroll system must have done this stuff! Is there an easy
>way to get the number?
>
>Adam
>

Adam


I'd do it like this - it would be nice to see an easier way as I maintain a Payroll system (in PROGRESS not Delphi).

My code doesn't handle Public Holidays which may or may not be important to you.  You'd need some sort of lookup table to check for those, unless you can work them out by formula (.. too hard in NZ  as we have regional holidays - easier to have a table )


Cheers

Jeff



uses
..., DateUtils;


procedure TForm1.Button1Click(Sender: TObject);
var
 iDays: integer;
 d: TDateTime;
begin
 iDays := 0;
 d := DateTimePicker1.Date;
 while d <= DateTimePicker2.Date do
 begin
   if DayOfTheWeek(d) < 6 then Inc(iDays);
     d := d + 1;
 end;
 Label1.Caption := IntToStr(iDays) + ' working days';
end;

end.


--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Adam;
there must be something in "ESBDates.pas"
http://www.esbconsult.com.au/downloads.html

Allan

"adam" <adam@nospamplease.fmfoods.co.uk> wrote in message
news:C54CFACA-5BD5-4C3B-8EA2-B56B874034C8@news.elevatesoft.com...
> Sorry if this is not a totally DBISAM focused question, but it is pure DB
& I am sure you
> guys must have had to solve this one sometimes!
>
>    Q.FieldByName('TotalDays').AsFloat :=
(Q.FieldByName('DateEnd').AsDateTime -
> Q.FieldByName('DateStart').AsDateTime);
>
> I am currently using this code snipet in Delphi to count the number of
days between 2
> dates. It works, though I have to check that both fields contain dates
etc.
>
> However now the customer (don't we all love them) wants a second field to
contain the
> week-day (i.e. count Monday - Friday, don't include Saturday & Sunday in
the count) count
> between 2 dates ...
>
> i.e. 1st February 2006 - 15th February 2006 is 15 days, but only 11 if you
just count
> Monday - Friday.
>
> --
>
> Someone who works on a Work / Payroll system must have done this stuff! Is
there an easy
> way to get the number?
>
> Adam
>

Dear Jeff,

Thanks a lot for this ... its pretty simple, but will definitely work. The kind of code that I would take hours to figure out.

The point of public holidays is a serious one. Right now my user hasn't thought of this, so I will be able to get away with your version ... but they will
figure it out eventually!

I guess that someone somewhere keeps a register of which days are public holidays in which countries (ideally it would just be a list of numbers for
each country for each year, so you could access it & reference it in code).

Does anyone out there have any idea of whether such a site / service exists ??

Adam Brett

adam


Here's a starter for 10

www.bank-holidays.com/index.htm

Roy Lambert

>I guess that someone somewhere keeps a register of which days are public holidays in which countries (ideally it would just be a list of numbers for
>each country for each year, so you could access it & reference it in code).
>
>Does anyone out there have any idea of whether such a site / service exists ??

Hi Adam,

check PSC Calendar from Putersoft. It has built in hollidays for some countries.
You can define hollidays also by yourself.

Putersoft SDK 2.03 is latest version of this component set. It is free now (it
was $148 before).  You can not find it at their site any more. They are not a
Delphi components vendor any more. You can find it at www.torry.net. I have
latest version 2.03 (with source), if you want to test it.

I am working with their FilterBox component (visual query builder). It works
great with Dbisam.

Regards,

Joze

Product info (old one year):

puterSoft.SDK
Version 2.03
Copyright (c) 1999-2003 puterSoft.Com

e-mail: support@putersoft.com
WWW: http://www.putersoft.com
(web site is active, but not for their Delphi components !!)

puterSoft.SDK is a freeware components library
for the developers working with Borland Delphi
and C++ Builder.

Currently puterSoft.SDK contains three modules:

PSC FilterBox includes different components allowing
to implement powerful but easy to use data filtering,
sorting and search features in your  applications.

PSC Calendar includes highly customizable and top
quality calendar and date-time editing controls which
support different appearance, themes and styles.

PSC FontColor includes great looking controls which
allows to implement state of the art color and font
picking like in Microsoft Office.

adam <adam@nospamfmfoods.co.uk> wrote on Tue, 7 Mar 2006 11:23:17 -0500


>I guess that someone somewhere keeps a register of which days are public holidays in which countries (ideally it would just be a list of numbers for
>each country for each year, so you could access it & reference it in code).
>
>Does anyone out there have any idea of whether such a site / service exists ??
>


adam


Best I can find is this one http://www.jours-feries.com/statique/index_pays2.html - though how you would access it in code I don't know.  Also remember that the days can vary by location.  At the link aforementioned link, there is one entry for New Zealand with days marked "(Westland only)", "(Auckland only)" etc., whereas there are separate entries for each of the Australian states.

My app has a user maintained table (Date, Location, HolidayName) and my suggested day counting code would include a lookup to see if the date was a holiday for all locations or for the employee's location.  In Delphi it would look something like :-


if (DayOfTheWeek(d) < 6)
   and not tblHols.Locate('Date;Location', VarArrayOf([d,'']), [])
   and not tblHols.Locate('Date;Location', VarArrayOf([d,'tblEmployeeLocation.AsString), [])   then Inc(iDays);

Cheers

Jeff



--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz