Calculating and showing Age Column on Sql Server as (xx Years, xx months)

Question

Possible Duplicate:
How to calculate age in T-SQL with years, months, and days

I have a problem with age calculation on Sql Server 2005. I have a customers table which has date of birth column. What i want is to create view which has an age column and this age column should show the age of a person by subtracting the date of birth from today(pretty simple).

I found a couple of good resources here but what i want exactly is to show the age by years and months. eg.(47 yrs, 3 months). This means i want to have both the year and month values and i want to add a string (either 'years', 'yrs', 'y', 'months', 'mon', 'm', ...) to the appropriate value and display it as eg. (25 years, 2 months). And if the person's age is just for example 3 months, it should display '3 months', not '0 years, 3 months'.

If the person's age is some years without any months, it should display eg. '5 years' not '5 years, 0 months'

Oh, I have already done that using c# but i want to create a column on the sql view so that i can just populate my gridview using the view without doing any calculation on my c# code. If it helps i can post my c# code here. Thanks

score 1 · Answer 1 · edited Nov 06 '19 at 14:29

Here's a format_age function:

CREATE FUNCTION format_age(
    @DOB datetime,
    @now datetime
)
RETURNS nvarchar(30)
BEGIN
    DECLARE @years int, @months int;
    IF @DOB > @now RETURN N'not born yet';
    SET @years = DATEDIFF(year, @DOB, @now);
    IF MONTH(@DOB) * 100 + DAY(@DOB) > MONTH(@now) * 100 + DAY(@now)
        SET @years = @years - 1;
    SET @months = DATEDIFF(month, DATEADD(year, @years, @DOB), @now);
    IF DAY(@DOB) > DAY(@now)
        SET @months = @months - 1;
    RETURN CASE
        WHEN @years = 0 THEN CONVERT(nvarchar(30), @months) + N' months'
        WHEN @months = 0 THEN CONVERT(nvarchar(30), @years) + N' years'
        ELSE CONVERT(nvarchar(30), @years) + N' years, ' +
            CONVERT(nvarchar(30), @months) + N' months'
    END;
END
GO

Here's some test cases:

SELECT '2011-02-16' AS dt, dbo.format_age('2011-02-16', '2012-03-16') AS age UNION
SELECT '2011-03-16',       dbo.format_age('2011-03-16', '2012-03-16')        UNION
SELECT '2011-03-17',       dbo.format_age('2011-03-17', '2012-03-16')        UNION
SELECT '2012-03-16',       dbo.format_age('2012-03-16', '2012-03-16')        UNION
SELECT '2012-03-17',       dbo.format_age('2012-03-17', '2012-03-16')

And here's the result^*:

| dt          | age               |
|-------------|-------------------|
| 2011-02-16  | 1 years, 1 months |
| 2011-03-16  | 1 years           |
| 2011-03-17  | 11 months         |
| 2012-03-16  | 0 months          |
| 2012-03-17  | not born yet      |

^{*Reference date is 2012-03-16}

shows this error when executing the query on sql server management studio. Msg 139, Level 15, State 1, Procedure format_age, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Procedure format_age, Line 10 Must declare the scalar variable "@years". Msg 139, Level 15, State 1, Procedure format_age, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Procedure format_age, Line 13 Must declare the scalar variable "@months". Msg 137, Level 15, State 2, Procedure format_age, Line 15 Must declare the scalar variable "@years". — EngAbth9, Mar 17 '12 at 19:04
Assigning a value with the `DECLARE` statement was introduced in SQL Server 2008. I updated the answer to split it up into a separate `DECLARE` and `SET`. — Markus Jarderot, Mar 17 '12 at 22:22

score 0 · Answer 2 · answered Mar 16 '12 at 08:44

0

I think you should consider localization issues with that solution (and different standards to calculate the age in different countries). If you do not care about that you may:

Write a SQL scalar function to calculate the age (see these posts on MSDN).
Write a C# function and integrate it in SQL Server (see this article on SQLTeam).

answered Mar 16 '12 at 08:44

Adriano Repetti

65,416
20
137
208

Thanks, i just converted my C# code to an Sql function which i used in my view. – EngAbth9 Mar 17 '12 at 19:02

Phil · Answer 3 · 2012-03-16T08:52:25.410

0

You need something like this:

SELECT *,
  case when DATEDIFF(MONTH, Birthday, GETDATE()) < 12
  then 
    CONVERT(nvarchar(16), DATEDIFF(MONTH, Birthday, GETDATE())) + ' months'
  else 
    CONVERT(nvarchar(16), DATEDIFF(YEAR, Birthday, GETDATE())) + ' years, ' + 
    CONVERT(nvarchar(16), DATEDIFF(MONTH, Birthday, GETDATE()) % 12 ) + ' months'
  end 
FROM dbo.CUSTOMER

edited Mar 16 '12 at 08:52

answered Mar 16 '12 at 08:47

Phil

42,255
9
100
100

This was also a good one but it gives wrong answers sometimes. – EngAbth9 Mar 17 '12 at 19:11

score 0 · Answer 4 · answered Mar 16 '12 at 09:02

Maybe something like this:

Test data

DECLARE @tbl TABLE(birthDay DATETIME)
INSERT INTO @tbl
VALUES
    ('2004-04-05'),
    ('2001-02-05')

Query

;WITH Years
AS
(   
    SELECT
        DATEDIFF(yy, tbl.birthDay, GETDATE()) - CASE WHEN (MONTH(tbl.birthDay) > MONTH(GETDATE())) OR (MONTH(tbl.birthDay) = MONTH(GETDATE()) AND DAY(tbl.birthDay) > DAY(GETDATE())) THEN 1 ELSE 0 END AS Years,
        tbl.birthDay
    FROM
        @tbl AS tbl 
), BithDayWithMonth
AS
(
    SELECT
        Years.Years,
        DATEDIFF(m, years.tmp, GETDATE()) - CASE WHEN DAY(Years.birthDay) > DAY(GETDATE()) THEN 1 ELSE 0 END AS Months
    FROM
        (
            SELECT
                DATEADD(yy, Years.Years, Years.birthDay) AS tmp,
                Years.Years,
                Years.birthDay
            FROM
                Years
        ) AS years
)
SELECT
    (CASE WHEN BithDayWithMonth.Years=0 THEN '' ELSE CAST(BithDayWithMonth.Years AS VARCHAR(10))+' years' END)+
    (CASE WHEN BithDayWithMonth.Months=0 THEN '' ELSE (CASE WHEN NOT BithDayWithMonth.Years=0 THEN ', ' ELSE '' END) +
    CAST(BithDayWithMonth.Years AS VARCHAR(10))+' months' END)
FROM
    BithDayWithMonth

Query executes successfully but there are some irregular values like 24 years, 24 months (using 3/17/2012 as now and 3/20/1987 as Birth Date) — EngAbth9, Mar 17 '12 at 19:07

Calculating and showing Age Column on Sql Server as (xx Years, xx months)

4 Answers4

Linked