Solved

Extrack DOB between 2 Dates

Posted on 2004-03-27
26
284 Views
Last Modified: 2010-04-05
Hi,

I get my customers next birthday and enter it into a database (Paradox), the reason I don't ask the date of birth is because sometimes the refuse to give it, so.

I extract the customers birthday as follows:
procedure TForm1.Button1Click(Sender: TObject);
 Var
   FToday: TDate;
   fD, fM,  fY : Word;
   begin
   Ftoday := (Now + 1);
   DecodeDate(FToday, FY , FM, FD);
 QueryDOB.Close;
   QueryDOB.SQL.Clear;
  QueryDOB.SQL.Add('SELECT DOB, Customer, Fax,  FirstName FROM Customer.DB  WHERE  (EXTRACT(MONTH FROM DOB) ='+(IntToStr(FM))+') and (EXTRACT(Day FROM DOB) ='+(IntToStr(FD))+')');
  QueryDOB.Open;
end;

This is only good if I want to send a card to arrive in the morning.

Is what I would like to do is run the program to get me the Birthdays of customers for say the next month.

I would run the program year after year and would still like to get the birthdays even though I have entered the birthday as say 27/03/2004

What do I need to ajust.

Thanks

Asw
0
Comment
Question by:Asw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 5
  • +1
26 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 10695870
Condition something like this.
Hope its clear
((MDOB = FM ) and (DDOB >= FD)) or ( ((MDOB  = FM+1 ) or (MDOB=1 and FM=12)) and (DDOB >= FD))
0
 
LVL 17

Expert Comment

by:mokule
ID: 10695878
Correction
((MDOB = FM ) and (DDOB >= FD)) or ( ((MDOB  = FM+1 ) or (MDOB=1 and FM=12)) and (DDOB <= FD))
0
 
LVL 1

Author Comment

by:Asw
ID: 10695997
Hi mokule,

I don't understand, can you put your example into my example.

Thanks
Andy
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:mokule
ID: 10696043
MDOB means EXTRACT(MONTH FROM DOB)
DDOB : EXTRACT(Day FROM DOB)
FD :   (IntToStr(FD))
FM :  (IntToStr(FM))

What about now?
0
 
LVL 1

Author Comment

by:Asw
ID: 10696056
Ok I'll give it a shot.

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10696067
procedure TForm1.Button1Click(Sender: TObject);
var
  FToday: TDate;
  fD,
  fM,
  fY:     Word;
begin
  Ftoday := (Now + 1);
  DecodeDate(FToday, FY , FM, FD);
  QueryDOB.Close;
  QueryDOB.SQL.Clear;
  QueryDOB.SQL.Text := ''
                     + ' SELECT DOB, Customer, Fax, FirstName'
                     + ' FROM Customer.DB'
                     + ' WHERE (EXTRACT(MONTH FROM DOB)='
                     + QuotedStr(IntToStr(FM))
                     + ') and (EXTRACT(Day FROM DOB) ='
                     + QuotedStr(IntToStr(FD))
                     + ')'
                     + '';
  QueryDOB.Open;
end;
0
 
LVL 17

Expert Comment

by:mokule
ID: 10696069
BTW I can't check it now. Is it correct in Paradox dialect SQL?
MONTH('03/12/1998')
DAY('03/12/1998')
0
 
LVL 1

Author Comment

by:Asw
ID: 10696143
Hi  mokule,

Yes it runs ok

It just needs to do what I need.

Thanks
Andy
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10696158
For next month:

procedure TForm1.Button1Click(Sender: TObject);
var
  FToday: TDate;
  fD,
  fM,
  fY:     Word;
  T:      string;
begin
  FToday := (Date + 1);
  DecodeDate(FToday, FY , FM, FD);
  FM := FM + 1;
  QueryDOB.Active := False;
  QueryDOB.SQL.Clear;
  T := ''
                     + ' SELECT DOB, Customer, Fax, FirstName'
                     + ' FROM Customer.DB'
                     + ' WHERE (EXTRACT(MONTH FROM DOB)='
                     + QuotedStr(IntToStr(FM))
                     + ')'
                     + '';
  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;

emil
0
 
LVL 45

Expert Comment

by:aikimark
ID: 10696382
Be sure to check your solutions in December.  Set your PC's clock to any December date and run the query.
0
 
LVL 17

Expert Comment

by:mokule
ID: 10696399
In my solution it is checked OK
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 100 total points
ID: 10697237
Ypu are right aikimark, in December my above example is incorrect, but below
For next month:

procedure TForm1.Button2Click(Sender: TObject);
var
  FToday: TDate;
  fD,
  fM,
  fY:     Word;
  T:      string;
begin
  FToday := (Date + 1);
  DecodeDate(FToday, FY , FM, FD);
  FM := FM + 1;
  if (FM>12) then
    FM := 1;
  QueryDOB.Active := False;
  T := ''
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (EXTRACT(MONTH FROM DOB)='
     + QuotedStr(IntToStr(FM))
     + ')'
     + '';
  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;

emil
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10701650
Hi Asw,
I could develop for you an example application which really does "Extract DOB between 2 Dates".

emil
0
 
LVL 1

Author Comment

by:Asw
ID: 10703751
Hi  esoftbg.


How many points?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 10704302
As a Local SQL exercise for me, how about:

procedure DOB_BetweenDates(FromDate: TDate, ToDate: TDate);
var
  FromMMDD: string;
  ToMMDD: string;
  DD,
  MM,
  YY:     Word;
  T:      string;
begin
  DecodeDate(FromDate, YY , MM, DD);
  FromMMDD := IntToStr(MM) + IntToStr(DD);
  DecodeDate(ToDate, YY , MM, DD);
  ToMMDD := IntToStr(MM) + IntToStr(DD);

  QueryDOB.Active := False;
  T := ''
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (CAST(EXTRACT(MONTH FROM DOB) AS CHAR(2)) || CAST(EXTRACT(DAY FROM DOB) AS CHAR(2)) ) BETWEEN '
     + QuotedStr(FromMMDD) + ' AND ' + QuotedStr(ToMMDD) ')'
     + '';
  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10704685
Hi Asw,
You will decide how many points if the example application works ok.
Deal ?

emil
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10704975
Sorry aikimark,
your code works only for regular case if the first month is less than the last one. Your code doesn't work for example:
From = 09/22  To 03/22 including whole months 10,11,12,1,2.

emil
0
 
LVL 45

Expert Comment

by:aikimark
ID: 10705387
I was afraid that I'd failed to address the crossing of a year boundary after I'd clicked the Submit button.

procedure DOB_BetweenDates(FromDate: TDate, ToDate: TDate);
var
  FromMMDD: string;
  ToMMDD: string;
  DD,
  MM,
  YY:     Word;
  T:      string;
begin
  DecodeDate(FromDate, YY , MM, DD);
  FromMMDD := IntToStr(MM) + IntToStr(DD);
  DecodeDate(ToDate, YY , MM, DD);
  ToMMDD := IntToStr(MM) + IntToStr(DD);

  QueryDOB.Active := False;
  If FromMMDD <= ToMMDD
  T := ''
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (CAST(EXTRACT(MONTH FROM DOB) AS CHAR(2)) || CAST(EXTRACT(DAY FROM DOB) AS CHAR(2)) ) BETWEEN '
     + QuotedStr(FromMMDD) + ' AND ' + QuotedStr(ToMMDD) ')'
     + '';
  Else
  T := ''
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (CAST(EXTRACT(MONTH FROM DOB) AS CHAR(2)) || CAST(EXTRACT(DAY FROM DOB) AS CHAR(2)) ) BETWEEN '
     + QuotedStr(FromMMDD) + ' AND ' + QuotedStr('1231')
     + ') UNION ALL '
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (CAST(EXTRACT(MONTH FROM DOB) AS CHAR(2)) || CAST(EXTRACT(DAY FROM DOB) AS CHAR(2)) ) BETWEEN '
     + QuotedStr('0101') + ' AND ' + QuotedStr(ToMMDD) ')'
     + '';

  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10705666
Hi aikimark,
your example code doesn't work 100% correct.
I don't know why ....

emil
0
 
LVL 45

Expert Comment

by:aikimark
ID: 10705913
hmmmmm.  My gut feel is that I've failed to force two character widths for the month and day parts and, thus, throwing off the collating sequence.

  FromMMDD := Format('<%.2d>', MM) + Format('<%.2d>', DD);

  ToMMDD := Format('<%.2d>', MM) + Format('<%.2d>', DD);
0
 
LVL 1

Author Comment

by:Asw
ID: 10706235
Ok Deal

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10706723
Ok Andy,
download from             http://www.geocities.com/esoftbg/
the file                         Q_20934559.zip
it contains the example application and sample Paradox database with 5 records.
You will see that my solution displays all 5 records, but aikimark's displays 4 for an unknown reason

emil
0
 
LVL 1

Author Comment

by:Asw
ID: 10712235
Hi esoftbg,

Looks Good to me, is 200 points ok?

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10712390
Hi Asw,
200 poits is ok for me, thanks!

emil
0
 
LVL 1

Author Comment

by:Asw
ID: 10717274
OK I will put a question which will say Points For esoftbg, you write some text and I will accept as an answer.

Thanks
Andy
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10721064
This IS NOT A NEW ANSWER: Asw awarded me for code downloaded from my site, but it is possible in the future my site to be destroyed for any reason. Let the accepted code be here:

unit Unit_Q_20934559;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, StdCtrls, Grids, DBGrids, Spin;

type
  TForm1 = class(TForm)
    QueryDOB: TQuery;
    Button1: TButton;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button2: TButton;
    Button3: TButton;
    sped_Day_First: TSpinEdit;
    sped_Day_Last: TSpinEdit;
    sped_Month_First: TSpinEdit;
    sped_Month_Last: TSpinEdit;
    MemoSQL: TMemo;
    Label1: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure sped_Day_FirstExit(Sender: TObject);
    procedure sped_Day_LastExit(Sender: TObject);
    procedure sped_Month_FirstExit(Sender: TObject);
    procedure sped_Month_LastExit(Sender: TObject);
  private   { Private declarations }
  public    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject); // Birthdays tomorrow
var
  FToday: TDate;
  fD,
  fM,
  fY:     Word;
  T:      string;
begin
  FToday := (Date);
  DecodeDate(FToday, FY , FM, FD);
  QueryDOB.Active := False;
  T :=''
    + ' SELECT DOB, Customer, Fax, FirstName'
    + ' FROM Customer.DB'
    + ' WHERE (EXTRACT(MONTH FROM DOB)='
    + QuotedStr(IntToStr(FM))
    + ') and (EXTRACT(Day FROM DOB) ='
    + QuotedStr(IntToStr(FD+1))
    + ')'
    + '';
  MemoSQL.Text := T;
  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;

procedure TForm1.Button2Click(Sender: TObject); // Birthdays next month
var
  FToday: TDate;
  fD,
  fM,
  fY:     Word;
  T:      string;
begin
  FToday := (Date);
  DecodeDate(FToday, FY , FM, FD);
  FM := FM + 1;
  if (FM>12) then
    FM := 1;
  QueryDOB.Active := False;
  T := ''
     + ' SELECT DOB, Customer, Fax, FirstName'
     + ' FROM Customer.DB'
     + ' WHERE (EXTRACT(MONTH FROM DOB)='
     + QuotedStr(IntToStr(FM))
     + ')'
     + '';
  MemoSQL.Text := T;
  QueryDOB.SQL.Text := T;
  QueryDOB.Open;
end;

procedure TForm1.Button3Click(Sender: TObject); // Birthdays between 2 dates
var
  DF:     Word;
  MF:     Word;
  DL:     Word;
  ML:     Word;
  WF:     Word;
  WL:     Word;
  W:      Word;
  S:      string;
  T:      string;
  TT:     string;
  R:      string;
begin
  DF := sped_Day_First.Value;
  MF := sped_Month_First.Value;
  DL := sped_Day_Last.Value;
  ML := sped_Month_Last.Value;
  if (((DF<=DL) and (MF=ML)) or (MF<>ML)) then
  begin
    QueryDOB.Active := False;

    S :=''
      + ' SELECT DOB, Customer, Fax, FirstName'
      + ' FROM Customer.DB WHERE ('
      + '((EXTRACT(DAY FROM DOB) >= '
      + QuotedStr(sped_Day_First.Text);
    R :=')' + '';

    if (MF=ML) then
    begin
      T :=') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_First.Text)
        + ') AND (EXTRACT(DAY FROM DOB) <= '
        + QuotedStr(sped_Day_Last.Text)
        + '))';
    end
    else
    if (ML-MF=1) then
    begin
      T :=') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_First.Text)
        + ')) OR ((EXTRACT(DAY FROM DOB) <= '
        + QuotedStr(sped_Day_Last.Text)
        + ') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_Last.Text)
        + '))';
    end
    else
    if ((MF=12) and (ML=1)) then
    begin
      T :=') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr('12')
        + ')) OR ((EXTRACT(DAY FROM DOB) <= '
        + QuotedStr(sped_Day_Last.Text)
        + ') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr('1')
        + '))';
    end
    else
    if ((ML-MF)>1) then
    begin
      TT := '(';
      for W := MF+1 to ML-1 do
        TT := TT + IntToStr(W) + ',';
      Delete(TT, Length(TT), 1);
      TT := TT + ')';
      T :=') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_First.Text)
        + ')) OR ((EXTRACT(MONTH FROM DOB) IN '
        + TT
        + ')) OR ((EXTRACT(DAY FROM DOB) <= '
        + QuotedStr(sped_Day_Last.Text)
        + ') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_Last.Text)
        + '))';
    end
    else
    if ((ML-MF)<0) then
    begin
      TT := '(';
      WF := MF + 1;
      if (WF=13) then
        WF := 1;
      WL := ML - 1;
      if (WL<1) then
        WL := 12;
      if (MF>ML) then
      begin
        if (WF<=WL) then
        begin
          for W := WF to WL do
            TT := TT + IntToStr(W) + ',';
        end
        else
        begin
          for W := WF to 12 do
            TT := TT + IntToStr(W) + ',';
          for W := 1 to WL do
            TT := TT + IntToStr(W) + ',';
        end;
      end
      else
        for W := WL to WF do
          TT := TT + IntToStr(W) + ',';
      Delete(TT, Length(TT), 1);
      TT := TT + ')';
      if (MF>ML) then
        TT := ' IN ' + TT
      else
        TT := ' NOT IN ' + TT;
      T :=') AND (EXTRACT(MONTH FROM DOB) = '
        + QuotedStr(sped_Month_First.Text)
        + ')) OR ((EXTRACT(MONTH FROM DOB)'
        + TT
        + ')) OR ((EXTRACT(DAY FROM DOB) <= '
        + QuotedStr(sped_Day_Last.Text)
        + ') AND (EXTRACT(MONTH FROM DOB) <= '
        + QuotedStr(sped_Month_Last.Text)
        + '))';
    end;

    TT :=  S + T + R;
    QueryDOB.SQL.Text := TT;
    MemoSQL.Text := TT;
    QueryDOB.Open;
  end
  else
    ShowMessage('Please enter a valid period');
end;

procedure TForm1.sped_Day_FirstExit(Sender: TObject);
begin
  if (sped_Day_First.Text='') then
    sped_Day_First.Value := 1;
  if (sped_Day_First.Value<1) then
    sped_Day_First.Value := 1;
  if (sped_Day_First.Value>31) then
    sped_Day_First.Value := 31;
end;

procedure TForm1.sped_Day_LastExit(Sender: TObject);
begin
  if (sped_Day_Last.Text='') then
    sped_Day_Last.Value := 1;
  if (sped_Day_Last.Value<1) then
    sped_Day_Last.Value := 1;
  if (sped_Day_Last.Value>31) then
    sped_Day_Last.Value := 31;
end;

procedure TForm1.sped_Month_FirstExit(Sender: TObject);
begin
  if (sped_Month_First.Text='') then
    sped_Month_First.Value := 1;
  if (sped_Month_First.Value<1) then
    sped_Month_First.Value := 1;
  if (sped_Month_First.Value>12) then
    sped_Month_First.Value := 12;
end;

procedure TForm1.sped_Month_LastExit(Sender: TObject);
begin
  if (sped_Month_Last.Text='') then
    sped_Month_Last.Value := 1;
  if (sped_Month_Last.Value<1) then
    sped_Month_Last.Value := 1;
  if (sped_Month_Last.Value>12) then
    sped_Month_Last.Value := 12;
end;

end.

emil
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question