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

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

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

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

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

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

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

Am interested in this stored procedure but the year and months routines
do not seem to be working correctly.

Could you provide the correct code?
This does not seem to work
/*** Calculate the Years ***/

ELSE

--
Posted via http://dbforums.com