Calculate age when date month and year are in different columns in sql server

Question

I have a database in which date of birth is stored in three different column as a nvarchar first column is for date second for month and third for year now how can I calculate age using these column -Thanks

possible duplicate of [How to calculate age in T-SQL with years, months, and days](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) — Jaugar Chang, Sep 18 '14 at 12:46

score 2 · Accepted Answer · answered Sep 18 '14 at 07:13

This should work it out for you:

declare @t table (DOBYear nvarchar(19), DOBMonth nvarchar(7),
                  DOBDay nvarchar(13))
insert into @t(DOBYear,DOBMonth,DOBDay) values ('1978','05','17')

select (DATEPART(year,CURRENT_TIMESTAMP) - DOBYear)
    - CASE
        WHEN DATEPART(month,CURRENT_TIMESTAMP) < DOBMonth THEN 1
        WHEN DATEPART(month,CURRENT_TIMESTAMP) = DOBMonth AND
             DATEPART(day,CURRENT_TIMESTAMP) < DOBDay THEN 1
        ELSE 0 END
from @t

Basically, you just subtract the years values, and then there are a couple of cases when that will overcount by 1, so we then adjust that value if necessary.

We're also relying on the fact that the DATEPART function returns ints and then data-type precedence rules will then force the nvarchar values to also be converted to ints before the comparisons occur. If you want to make the conversions explicit, you can wrap each column reference with a CONVERT(int,<column>).

Azar · Answer 2 · 2014-09-18T08:52:54.950

Try this

create function dbo.dob
(
@dob datetime
)
returns datetime
as
begin
declare @out datetime
select @out = dateadd(yy,datediff(yy,convert(date,@dob),getdate())-1,@dob)
return @out
end

For Only Years:

select  convert(varchar,case    when mm > month(getdate()) 
                then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                when mm = month(getdate()) and dd>day(getdate()) 
                then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                else datediff(yy,convert(date,yy+mm+dd),getdate())end)+' Years '
 from table

For entire dob including days:

select  convert(varchar,case    when mm > month(getdate()) then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                when mm = month(getdate()) and dd>day(getdate()) then datediff(yy,convert(date,yy+mm+dd),getdate())-1 
                else datediff(yy,convert(date,yy+mm+dd),getdate())end)+' Years '+
        convert(varchar,case    when month(getdate()) = mm and dd>day(getdate())  then datediff(mm,dbo.dob(convert(date,yy+mm+dd)),getdate())-1%12 
                else datediff(mm,dbo.dob(convert(date,yy+mm+dd)),getdate())%12 end)+' Months And '+
        convert(varchar,datediff(dd,dbo.dob(yy+mm+dd),getdate())%case when year(getdate())%4 = 0 and mm > 2 then 366 else 365 end)+' Days '
from table

The OP has updated their question. They actually want the age, not the DOB. — Damien_The_Unbeliever, Sep 18 '14 at 07:24

score 1 · Answer 3 · edited Sep 18 '14 at 09:06

1

Query:

  SELECT CONVERT(DATETIME,[YEAR] + '-' + [MONTH] + '-' + [Day],101) AS DOB
  FROM DateOfBirth

Output:

DateOfBirth

edited Sep 18 '14 at 09:06

Infinite Recursion

6,511
28
39
51

answered Sep 18 '14 at 07:16

Mukund

1,679
1
11
20

The OP has updated their question. They actually want the age, not the DOB. – Damien_The_Unbeliever Sep 18 '14 at 07:23

score 1 · Answer 4 · edited Jan 14 '18 at 00:22

If the person was born a month previous to the actual month, or a day before or equal to today of this month, then the age is the number of years from the year of birth to the current year. If not, is the same, less 1.

CREATE TABLE BirthDates
(YEAR INT,
MONTH INT,
DAY INT)

Test data for today (2014-09-18):

INSERT INTO BirthDates VALUES(2013,9,19) -- 0 the frist birthday is still to come
INSERT INTO BirthDates VALUES(2013,9,18) -- 1 today is the first birthday
INSERT INTO BirthDates VALUES(2013,9,17) -- 1 the first birthday was yesterday
INSERT INTO BirthDates VALUES(2013,8,31) -- 1 the first birthday was last month
INSERT INTO BirthDates VALUES(2013,10,1) -- 0 the first birthday will be next month

Required query

SELECT B.*,
    CASE WHEN (MONTH(GETDATE()) > B.MONTH 
               OR (MONTH(GETDATE()) = B.MONTH AND DAY(GETDATE()) >= B.DAY))
        THEN YEAR(GETDATE()) - B.YEAR 
        ELSE YEAR(GETDATE()) - B.YEAR - 1 END AS AGE
FROM BirthDates B

When I'm asked abut my age I say "I'm 40" I dont say "I'm 40 years, 3 months, 12 days, 7 hours, 35 minutes old". Do you say so? — JotaBe, Sep 18 '14 at 08:24

Calculate age when date month and year are in different columns in sql server

4 Answers4

Query:

Output: