Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Post by Gremoi » Fri, 20 Sep 2002 23:31:53



Hi,

I'm looking for a Transact-SQL algorithm or Stored Procedure to
calculate a period between 2 given dates (preferably on SQL7.0).

The results that I require are the Years, Months and Days, i.e.

Date 1: 2000/08/01
Date 2: 2002/09/19

Result: 2 (years), 1 (month), 18/(19?) (days)

I've tried with DATEDIFF, the year is correct, but the problem starts
with the days and the months.

Your help will be greatly appreciated.

Regards,

Gremoire

 
 
 

Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Post by Alejandro Mes » Fri, 20 Sep 2002 23:52:53


Try,










Result:

years       months      days        
----------- ----------- -----------
2           1           18

(1 row(s) affected)

AMB


> Hi,

> I'm looking for a Transact-SQL algorithm or Stored Procedure to
> calculate a period between 2 given dates (preferably on SQL7.0).

> The results that I require are the Years, Months and Days, i.e.

> Date 1: 2000/08/01
> Date 2: 2002/09/19

> Result: 2 (years), 1 (month), 18/(19?) (days)

> I've tried with DATEDIFF, the year is correct, but the problem starts
> with the days and the months.

> Your help will be greatly appreciated.

> Regards,

> Gremoire


 
 
 

Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Post by Steve Kas » Fri, 20 Sep 2002 23:52:53


Gremoire,

  This kind of calculation has some strange properties, and
it can be done in more than one way - if you assume B is one
month later than A to mean B has the same day of the month
as A, then what do you want to say about

Suppose

Date 1: 2000/01/31
Date 2: 2000/03/01
is this 1 months and 1 day, or 1 month and -1 days,
or 0 months and 30 days?

Date 1: 2000/01/30
Date 2: 2000/03/01
does this give a different answer from the previous one?

Date 1: 2000/01/31
Date 2: 2000/02/29
is this 1 months and 0 days, or 0 months and 29 days,
or 1 month and -2 days?

Date 1: 2000/01/29
Date 2: 2000/02/29
does this give a different answer from the previous one?

The best start is to give a careful definition of what answers
you want.

Steve Kass
Drew University


> Hi,

> I'm looking for a Transact-SQL algorithm or Stored Procedure to
> calculate a period between 2 given dates (preferably on SQL7.0).

> The results that I require are the Years, Months and Days, i.e.

> Date 1: 2000/08/01
> Date 2: 2002/09/19

> Result: 2 (years), 1 (month), 18/(19?) (days)

> I've tried with DATEDIFF, the year is correct, but the problem starts
> with the days and the months.

> Your help will be greatly appreciated.

> Regards,

> Gremoire

 
 
 

Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Post by Gremoi » Sat, 21 Sep 2002 16:06:17


Hi Steve,

Definition: The project I'm cirrently working is written in Delphi.
One of the details screen, for argument sake is a personal details
screen, including the person's Date of Birth. There is also a
calculated field on the screen giving the person's age in the the
following break down: Years, Months, Days - where the Years are the
persons's age in years and the Months and the Days are the Months and
Days already past since his last birthday.

Thus taking your examples:

Quote:> Suppose

> Date 1: 2000/01/31
> Date 2: 2000/03/01
> is this 1 months and 1 day, or 1 month and -1 days,
> or 0 months and 30 days?

The answer to this one will most probably be 1 month and 1 day
(although it might be argued to be 0 months and 30 days)

Quote:

> Date 1: 2000/01/30
> Date 2: 2000/03/01
> does this give a different answer from the previous one?

The answer to this one will differ from the one above, since there is
a day difference between 30 and 31.

Quote:

> Date 1: 2000/01/31
> Date 2: 2000/02/29
> is this 1 months and 0 days, or 0 months and 29 days,
> or 1 month and -2 days?

The answer to this one will most probably be 1 month and 0 days
(although it might be argued to be 0 months and 29 days)

Quote:

> Date 1: 2000/01/29
> Date 2: 2000/02/29
> does this give a different answer from the previous one?

The answer to this one will most probably be 1 month and 0 days
(although it might be argued to be 0 months and 29 days)

Here is example 1 of Delphi code to calculate this.
<..........>
<...snip...>
<..........>
begin
  If (plEmployee_Details['DependantDateOfBirth'] = NULL) OR
     (plEmployee_Details['DependantDateOfBirth'] = 0) then        
   Begin
   DBText12.Font.Color := clBlue;
   Text := 'Dependant Date of Birth not entered';
   End
  Else
   Begin
   DBText12.Font.Color := clBlack;
   TodayDate := CurrentDate;
   DecodeDate(CurrentDate, YearA, MonthA, DayA);
   Birthdate := rcDependants['Date of Birth'];
   DecodeDate(BirthDate, YearB, MonthB, DayB);
   DayPeriod := DayA - DayB;
   If DayPeriod >= 0 Then
      MonthPeriod := MonthA - MonthB
   Else Begin
                MonthA := MonthA - 1;
              If MonthB = (4) or (6)  or (9) or (11) Then
                   DaysInMonth := 30
                   Else If MonthB = 2 Then
                        begin
                          If {((YearA and 3) = 0) and} ((YearA mod 100 > 0)
                                        or (YearA mod 400 = 0)) = True Then
                                    IsLeap := True;
                          If IsLeap Then        
                             DaysInMonth := 29
                          Else
                             DaysInMonth := 28;
                        end
              Else DaysInMonth :=31;
                DayPeriod := DayPeriod + DaysInMonth;
           End;

   MonthPeriod := MonthA - MonthB;
        If MonthPeriod < 0 Then
           begin
                YearA := YearA - 1;
                MonthPeriod := MonthPeriod + 12;                
           end;

   YearPeriod := YearA - YearB;
        If MonthPeriod = 12 Then
           begin
                MonthPeriod := 0;
                YearPeriod := YearPeriod + 1;
           end;                

  Text := '';
  If YearPeriod > 0 Then
        Begin
                Text := Text + IntToStr(YearPeriod) + ' year(s)  ';
        End;
  If MonthPeriod > 0 Then
        Begin
                Text := Text + IntToStr(MonthPeriod) + ' month(s)  ';
        End;
  If DayPeriod >= 0 Then
        Begin
                Text := Text + IntToStr(DayPeriod) + ' day(s)';
        End;
  End;
end;  
<..........>
<...snip...>
<..........>

BUT the following example gives a different result (+- 2 days on the
previous example), which by my Project Manager is a reasonable margin
of error. (Example 2 is most widely used througout our system. Example
1 was an attempt to duplicate example 2, since example one's RAD
environment does not support all the functionality as Delphi, although
it uses some of Delphi's functionality.)

<..........>
<...snip...>
<..........>
procedure DateDiffBetween(TheDate1, TheDate2: TDateTime; var Years,
Months, Days: Word);
var YearCount, MonthCount, DaysCount: Integer;
    yy1, yy2, mm1, mm2, dd1, dd2: Word;
begin
     DecodeDate(TheDate1, yy1, mm1, dd1);
     DecodeDate(TheDate2, yy2, mm2, dd2);
     YearCount := Abs(yy1 - yy2);
     if dd1 >= dd2 then
     begin
          DaysCount := dd1 - dd2;
     end else
     begin
          DaysCount := (DaysInMonth(Date) - dd2) + dd1;
     end;
     if YearCount > 0 then Dec(YearCount);
     if yy1 = yy2 then
     begin
          MonthCount := Abs(mm1 - mm2);
          if mm1 < mm2 then
          begin
               if dd1 > dd2 then Dec(MonthCount);
          end;
          if mm1 > mm2 then
          begin
               if dd2 > dd1 then Dec(MonthCount);
          end;
     end else
     begin
          if yy1 < yy2 then
          begin
               MonthCount := (12 - mm1) + mm2;
               if dd1 > dd2 then Dec(MonthCount);
          end else
          begin
               MonthCount := (12 - mm2) + mm1;
               if dd2 > dd1 then Dec(MonthCount);
          end;
     end;
     Years := YearCount;
     Months := MonthCount;
     if Months > 11 then
     begin
          Months := Months - 12;
          Years := Years + 1;
     end;
     Days := DaysCount;
end;
<..........>
<...snip...>
<..........>

I wanted to know whether it will be possible to incorporate this
functionality into a stored procedure so that the calculated age
fields could be returned from the database via a stored procedure into
our report engine.

If possible a stored procedure solution of Example 2 will be much
appreciated.

Regards,

Gremoire

 
 
 

Date Calculations : Period Between 2 Dates (AGE) in Years, Months and Days

Post by Gremoi » Sat, 05 Oct 2002 17:42:37


Hi All,

I found a solution for my problem (I found the function that does the
calculation and transcribed it into TSQL, in the form of a stored
procedure).

Please find the Stored Procedure included:

<code>*************************************************

  /*****************************************************/
 /*** New Stored Procedure : SEC_PeriodBetweenDates ***/
/*****************************************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SEC_PeriodBetweenDates]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[SEC_PeriodBetweenDates]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







/*** Calculate the Years ***/


ELSE



ELSE

/*** Calculate the Months ***/

  BEGIN







  END
ELSE
  BEGIN

         BEGIN




         END
       ELSE
         BEGIN




         END
  END

/*** Calculate the Days ***/


ELSE


'-01' AS DATETIME)-1)) -


  BEGIN


  END



     ELSE



     ELSE



     ELSE


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<code>*************************************************

This is al nice and dandy if I'm able to execute the stored procedure,
but I also need to to be able to do this in a SELECT Statement.

A sample code of what I need is included and should give the exact
same results as with the stored procedure.

<code>*************************************************

use pubs

SELECT hire_date, 'This is where the calculation should take place' AS
PeriodHired
FROM employee

<code>*************************************************

So, if anybody can be of assistance once again, it would be really
appreciated.

Regards,

Gremoire