Solved

Extrack DOB between 2 Dates

Posted on 2004-03-27
26
281 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax Check Delphi Seattle IOS app without MAC ? 1 97
Path  to current project in Delphi. 2 79
Printing problem 2 93
Tvertscrollbox like a whatsapp layout 5 27
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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