Board index » delphi » How to get Birth Dates

2005-05-15 04:19:47 PM
delphi60
Hi All,
I'm using D7, ADO, and MS SQL2000
I created an app that stores the employees information. (Name, Surname,
Race, Gender, BirthDate, etc.)
I now have to create a Birthdate report to show all the employees who's
birthdates falls within as specific period. Now the problem that I have is
the employee's original birthdate was 11-April-1976 and now we are in 2005,
but I want to see all the employees who's birthdates falls between
01-April-2005 and 30-April-2005. How do I Ignore the year.
Below is my Procedure that I have so far:
procedure TBirthDatesReportForm.LoadData;
Var FromDate, ToDate : TDate;
begin
//Load all the data into the grid
Grid.RowCount := 2;
Grid.ClearRows(1,1);
//Ok Now get the data
With DataModuleForm.ADOQuery do
begin
FromDate := Trunc(FromDateBox.Date);
ToDate := Trunc(ToDateBo.Date);
SQL.Clear;
SQL.Add('Select Operation, Current_Department, IndustryNo, Name,');
SQL.Add('Surname, Initials, Designation, BirthDate');
SQL.Add('from EmployeeView');
SQL.Add('Where BirthDate>= :FromDate and BirthDate <= :ToDate');
Parameters.ParamValues['FromDate'] := FromDate;
Parameters.ParamValues['ToDate'] := ToDate;
DisableControls;
Open;
if IsEmpty = False then
begin
While NOT EOF do
begin
Grid.Cells[0,Grid.RowCount-1] :=
FieldByName('Operation').AsString;
Grid.Cells[1,Grid.RowCount-1] :=
FieldByName('Current_Department').AsString;
Grid.Cells[2,Grid.RowCount-1] :=
FieldByName('IndustryNo').AsString;
Grid.Cells[3,Grid.RowCount-1] := FieldByName('Name').AsString;
Grid.Cells[4,Grid.RowCount-1] :=
FieldByName('Surname').AsString;
Grid.Cells[5,Grid.RowCount-1] :=
FieldByName('Initials').AsString;
Grid.Cells[6,Grid.RowCount-1] :=
FieldByName('Designation').AsString;
Grid.Cells[7,Grid.RowCount-1] :=
FormatDateTime('dd-MMM',FieldByName('BirthDate').AsDateTime);
Grid.RowCount := Grid.RowCount+1;
Grid.ClearRows(Grid.RowCount-1,1);
Next;
end;
//Ok remove the last row as it is empty
Grid.RowCount := Grid.RowCount-1;
end;
Close;
EnableControls;
end;
Thanx,
Peet
 

"Peet Koekemoer" <XXXX@XXXXX.COM>pí¹e v diskusním pøíspìvku
if FromDate and ToDate dates are meant INCLUSIVE, then you can
use something like this:
select ...... from ......
where DatePart(yy, :FromDate -1.1 - BirthDate) < DatePart(yy, :ToDate -
BirthDate)
regards
--
Roman
mail: XXXX@XXXXX.COM
URL: www.rksolution.cz
 

Thanx, so easy when you know what to do!
"Roman Krejci" <XXXX@XXXXX.COM>writes

a simpler syntax is to use the T-Sql function "between" and then you do not need the
datepart code at all.
 

"Dennis Passmore" <XXXX@XXXXX.COM>píse v diskusním
príspevku news:XXXX@XXXXX.COM...
provide the solution that makes use of "between"?
Thanks in advance
Roman
 

Hi,
what about:
SELECT Name, Surname, BirthDate
FROM EmployeeView
WHERE MONTH(BirthDate) = 4; /* or EXTRACT(MONTH FROM BirthDate) */
which should pick up any birthdates through april.
Niels
"Peet Koekemoer" <XXXX@XXXXX.COM>skrev i en meddelelse

"Niels K" <XXXX@XXXXX.COM>pí¹e v diskusním pøíspìvku
is April (or any *exact month* for that matter).
A general period is somewhat tougher to solve.
Roman
 

Hi Roman,
well, it still is applicable with a little tweak as in:
SELECT Name, Surname, BirthDate
FROM EmployeeView
WHERE ((MONTH(BirthDate) * 100) + DAY(BirthDate)) BETWEEN 0401 AND 0430
where the last two digits in the BETWEEN parameters represents the dates and
the preceding digit(s) represent the month(s), in this case april 1 to april
30.
Niels
"Roman Krejci" <XXXX@XXXXX.COM>skrev i en meddelelse

"Roman Krejci" <XXXX@XXXXX.COM>pí¹e v diskusním pøíspìvku
is not that perfect. :FromDate - Birthdate as well as :ToDate - Birthdate
returns number of days between pairs of the dates. When those number
of days are expressed in years, and this number of years is different
for the two dates, then anniversary must have occurred in between.
However, because of the leap years we sometime get improper results -
the number of years that corresponds to 366 days depends on the year.
Therefore it seems necessary to "shift" all periods into the same
year (substract january 1st of the proper year from all dates) , and in this
construct "BETWEEN" is a logical choice:
Select ..... from ......
where
BirthDate-Convert(Datetime,'01/01/'+STR(Year(BirthDate)),101) /*
substract January 1st in Year of Birth date */
between
(:FromDate-Convert(Datetime,'01/01/'+STR(Year(:FromDate),101)) /*
substract January 1st in Year of FromDate */
and
(:ToDate-Convert(Datetime,'01/01/'+STR(Year(:FromDate),101)) /* substract
January 1st in Year of FromDate */
thanks and regards
Roman
 

"Niels K" <XXXX@XXXXX.COM>pí¹e v diskusním pøíspìvku
FromDate = feb 29 2004
ToDate = jan 5 2005
then I give up :-)
regards
Roman
 

I can not see how between will do the trick.
"Roman Krejci" <XXXX@XXXXX.COM>writes

Roman,
you're right, I didn't think of interval spans across new year where the
from-date-month>to-date-month ;-)
Best regards
Niels
"Roman Krejci" <XXXX@XXXXX.COM>skrev i en meddelelse

Roman,
SQL still can do a lot of tricks:
SELECT Name, Surname, BirthDate
FROM EmployeeView
WHERE
((:FromMonthDate>:ToMonthDate) /* across new year */
AND
((MONTH(BirthDate)*100) + DAY(BirthDate))>= :ToMonthDate
OR
((MONTH(BirthDate)*100) + DAY(BirthDate)) <= :ToMonthDate)
OR
((:FromMonthDate <= :ToMonthDate) /* within same year */
AND
((MONTH(BirthDate)*100) + DAY(BirthDate))
BETWEEN :FromMonthDate AND :ToMonthDate)
where :FromMonthDate/:ToMonthDate are the date limits in the MMDD format.
I don't think leap years should cause any problems.
Best regards
Niels
"Niels K" <XXXX@XXXXX.COM>skrev i en meddelelse