Solved

Extrack DOB between 2 Dates

Posted on 2004-03-27
26
278 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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi mokule,

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

Thanks
Andy
0
 
LVL 17

Expert Comment

by:mokule
Comment Utility
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
Comment Utility
Ok I'll give it a shot.

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
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
Comment Utility
Hi  mokule,

Yes it runs ok

It just needs to do what I need.

Thanks
Andy
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
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
Comment Utility
In my solution it is checked OK
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 100 total points
Comment Utility
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
Comment Utility
Hi Asw,
I could develop for you an example application which really does "Extract DOB between 2 Dates".

emil
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:Asw
Comment Utility
Hi  esoftbg.


How many points?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
Hi Asw,
You will decide how many points if the example application works ok.
Deal ?

emil
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
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
Comment Utility
Hi aikimark,
your example code doesn't work 100% correct.
I don't know why ....

emil
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
Ok Deal

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
Comment Utility
Hi esoftbg,

Looks Good to me, is 200 points ok?

Andy
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
Hi Asw,
200 poits is ok for me, thanks!

emil
0
 
LVL 1

Author Comment

by:Asw
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now