Hello

I have a Date field that records a booking date, and I need to display the number of whole months that have elapsed since that date. In Delphi I use a
complicated MonthsBetweenDates method that I can't reproduce in SQL.

Is there a way to do that with intervals? The calculation is StartDate - current_date, expressed as an integer.

Thanks for your help.

Regards & TIA

Peter

Peter,

> Is there a way to do that with intervals? The calculation is StartDate - current_date, expressed as an integer.

select (Current_Date - StartDate) month from MyTable

should do the trick. Please not the missing "interval" in calculations
like this and the parenthesis. In the manual look here:
http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=1&topic=25

Regards Uli

Correction:

Please *note* the missing "interval" in calculations like this and the
parenthesis. In the manual look here:

Thanks Uli

That helps a lot -though I am still confused about intervals. I'll pick it up eventually.

I have another age calculation that I would like to do on the server side, rather than in a calculated persistant field. The system I currently use shows the
person's age in years and months, so I take the modulus of the year and 365...

  iD := trunc(Now) - trunc(tblPer.FieldByName('DOB').AsDateTime);     
  iY := iD div 365;
  iM := (iD mod 365) div 30;
  tblPer.FieldByName('AgeSCalc').AsString := Format('%d year(s) %d month(s)', [iY, iM]);

...which is close enough. I have lost some hair trying to work out this one, even after I found the "Calculating AGE" thread.

Is there a way to select this in EDB SQL?

Regards & TIA

Peter

Peter,

> That helps a lot -though I am still confused about intervals.

You are not alone.

> Is there a way to select this in EDB SQL?

How about this:

select DOB,
((Current_Date - DOB month) / 12 as Years,
((Current_Date - DOB) month) mod 12 as Months
from MyTable

or casted as VarChar:

select
cast(((Current_Date - DOB ) month) / 12 as VarChar(2)) + ' year(s) ' +
cast(((Current_Date - DOB ) month) mod 12 as Varchar(2)) + ' month(s)'
as Age
from MyTable

Regards Uli

Uli

Eeeeexcellent. Laid out like that it appears simple & straitforward.

Thanks for your help.

Peter

Peter,

> Eeeeexcellent. Laid out like that it appears simple & straitforward.

Glad to help.

Uli

Peter,

<<  Is there a way to select this in EDB SQL? >>

If you don't mind a specific type of formatting, you can do this very easily
with a year-month interval:

select DOB,
CAST((Current_Date - DOB) YEAR TO MONTH AS VARCHAR(20)) AS YearsMonths
from MyTable

The result will be something like '2-10'.

Also, remember that intervals show up as integers in Delphi:

Year-Month Intervals are 32-bit integers (TIntegerField)
Day-Time Intervals are 64-bit integers (TLargeIntField)

There will be code in the new EDB Manager that shows you how to use the
TField OnGetText/OnSetText events along with some EDB functions for showing
intervals in their proper textual form in Delphi.  Here it is for now:

Field setup:

procedure TMainForm.SetupFields(DataSet: TEDBDataSet);
var
  I: Integer;
begin
  with DataSet do
     begin
     for I:=0 to FieldCount-1 do
        begin
        with Fields[I] do
           begin
           case DataType of
              {$IFDEF EDB_UNICODE}
              ftWideString:
              {$ELSE}
              ftString:
              {$ENDIF}
                 begin
                 OnGetText:=GetFieldText;
                 DisplayWidth:=Min(CharDisplayWidth,DisplayWidth);
                 end;
              ftBytes,ftVarBytes:
                 begin
                 OnGetText:=GetBinaryText;
                 OnSetText:=SetBinaryText;
                 end;
              {$IFDEF EDB_UNICODE}
              ftWideMemo:
              {$ELSE}
              ftMemo:
              {$ENDIF}
                 begin
                 OnGetText:=GetBlobText;
                 OnSetText:=SetBlobText;
                 DisplayWidth:=CLOBDisplayWidth;
                 end;
              ftBlob:
                 begin
                 OnGetText:=GetBlobText;
                 DisplayWidth:=BLOBDisplayWidth;
                 end;
              ftDate,ftTime,ftDateTime:
                 begin
                 OnGetText:=GetDateTimeText;
                 OnSetText:=SetDateTimeText;
                 end;
              ftBCD:
                 begin
                 OnGetText:=GetNumericText;
                 OnSetText:=SetNumericText;
                 DisplayWidth:=20;
                 end;
              ftFloat:
                 begin
                 OnGetText:=GetNumericText;
                 OnSetText:=SetNumericText;
                 DisplayWidth:=16;
                 end;
              ftInteger:
                 begin
                 if (GetYearMonthIntervalTypeForField(FieldName) <>
ymUnknown) then
                    begin
                    OnGetText:=GetYearMonthIntervalText;
                    OnSetText:=SetYearMonthIntervalText;
                    end
                 else
                    OnGetText:=GetFieldText;
                 end;
              ftLargeInt:
                 begin
                 if (GetDayTimeIntervalTypeForField(FieldName) <>
dtUnknown) then
                    begin
                    OnGetText:=GetDayTimeIntervalText;
                    OnSetText:=SetDayTimeIntervalText;
                    end
                 else
                    OnGetText:=GetFieldText;
                 end
              else
                 OnGetText:=GetFieldText;
              end;
           DisplayWidth:=Max((Length(FieldName)+2),DisplayWidth);
           if FieldIsIndexed(DataSet,FieldName) then
              DisplayWidth:=(DisplayWidth+4);
           end;
        end;
     end;
end;

And here's the OnGetText/OnSetText code:

procedure TMainForm.GetYearMonthIntervalText(Sender: TField; var Text:
String;
                                            DisplayText: Boolean);
begin
  if DisplayText and Sender.IsNull then
     Text:='NULL'
  else
     begin
     if Sender.IsNull then
        Text:=''
     else
        Text:=Engine.YearMonthIntervalToSQLStr(Sender.AsInteger,
             TEDBDataSet(Sender.DataSet).GetYearMonthIntervalTypeForField(Sender.FieldName));
     end;
end;

procedure TMainForm.SetYearMonthIntervalText(Sender: TField; const Text:
String);
begin
  if (Text <> '') then
     Sender.AsInteger:=Engine.SQLStrToYearMonthInterval(Text,
        TEDBDataSet(Sender.DataSet).GetYearMonthIntervalTypeForField(Sender.FieldName))
  else
     Sender.Clear;
end;

procedure TMainForm.GetDayTimeIntervalText(Sender: TField; var Text: String;
                                          DisplayText: Boolean);
begin
  if DisplayText and Sender.IsNull then
     Text:='NULL'
  else
     begin
     if Sender.IsNull then
        Text:=''
     else
        Text:=Engine.DayTimeIntervalToSQLStr(TLargeIntField(Sender).AsLargeInt,
             TEDBDataSet(Sender.DataSet).GetDayTimeIntervalTypeForField(Sender.FieldName));
     end;
end;

procedure TMainForm.SetDayTimeIntervalText(Sender: TField; const Text:
String);
begin
  if (Text <> '') then
     TLargeIntField(Sender).AsLargeInt:=Engine.SQLStrToDayTimeInterval(Text,
        TEDBDataSet(Sender.DataSet).GetDayTimeIntervalTypeForField(Sender.FieldName))
  else
     Sender.Clear;
end;

--
Tim Young
Elevate Software
www.elevatesoft.com