Solved

# calculate the age of someone based on a birthdate stored in nvarchar(50) field

Posted on 2011-03-04
484 Views
Hi

Is there a way of calculating a persons age if his birthdate is stored in a nvarchar(50) type field? The birthdate format is yyyy-mm-dd

Lets say I have:

"select UID, birthdate from people" and I need age accurate to the day

0
Question by:Steynsk
• 4
• 3
• 3
• +4

LVL 33

Expert Comment

select UID, birthdate, datediff(year,birthdate,getdate())
from people
0

LVL 33

Expert Comment

scratch that, it doesn't take the month into account... stand by
0

LVL 5

Expert Comment

You may use type convert for converting your varchar to datetime

select datediff(yy, convert(datetime,birthdate), getdate())
0

LVL 142

Expert Comment

to convert the varchar in to datetime would be this expression:
convert(datetime, birthdate, 120)

from there, use the datediff function ...
0

LVL 15

Expert Comment

ALTER function [HEA].[GetAgeAtDate]
(
--<parameters>
@Birthdate datetime,    -- <param required="yes" description="Birth day"/>
@Date datetime          -- <param required="yes" description="Datetime to get age in years for"/>
--</parameters>
)
returns int
as
begin
declare @Age int

if (datepart(mm,@Date) < datepart(mm,@Birthdate))
or ((datepart(mm,@Date) = datepart(mm,@Birthdate)) and (datepart(dd,@Date) < datepart(dd,@Birthdate)))
begin
select @Age = datepart(yyyy,@Date) - datepart(yyyy,@Birthdate) - 1
end
else
begin
select @Age = datepart(yyyy,@Date) - datepart(yyyy,@Birthdate)
end

return @Age
end
0

LVL 5

Expert Comment

Try this.

declare @totalmonths int;
select @totalmonths  = datediff(mm, convert(datetime,'2011-01-02'), getdate())
select @totalmonths  / 12 AS Years, @totalmonths  % 12 AS Months
0

LVL 33

Expert Comment

select UID, birthdate,
datediff(year,birthdate,getdate())
- case when month(birthdate)>month(getdate()) then 1 else 0 end
- case when month(birthdate)=month(getdate()) and day(birthdate)>day(getdate()) then 1 else 0 end
as age
from people
0

LVL 22

Expert Comment

``````SELECT UID
,birthdate
,DATEDIFF(year,birthdate,getdate())
-CASE WHEN REPLACE(SUBSTRING(birthdate,6,5),'-','/') >CONVERT(varchar(5),getdate(),101)
THEN 1
ELSE 0
END age
FROM People
``````
0

LVL 33

Expert Comment

-- or, as a function:

create function getAge(@bd datetime)
returns INT
AS
BEGIN
declare @age int
select @age = datediff(yy,@bd,getdate())
- case when month(@bd)>month(getdate()) then 1 else 0 end
- case when month(@bd)=month(getdate()) and day(@bd)>day(getdate()) then 1 else 0 end
return @age
END
GO

select UID, birthdate, dbo.getAge(birthdate)
from people

0

LVL 1

Author Comment

Thank you all but it needs to be presice to the day... instead of the year..
A little late I received an other question so to be honest I posted the question twice. For the complete question see:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26864306.html
0

LVL 15

Expert Comment

my UDF is accurate to the day
0

LVL 1

Author Comment

Hi Derek,

Sorry I get:

The ALTER FUNCTION SQL construct or statement is not supported.....
0

LVL 15

Accepted Solution

derekkromm earned 500 total points
you can just do this, where @Date is the date you want to check from (i.e. @Date = getdate())

select
case when (datepart(mm,@Date) < datepart(mm,@Birthdate))
or ((datepart(mm,@Date) = datepart(mm,@Birthdate)) and (datepart(dd,@Date) < datepart(dd,@Birthdate)))
then
datepart(yyyy,@Date) - datepart(yyyy,@Birthdate) - 1
else
datepart(yyyy,@Date) - datepart(yyyy,@Birthdate)
end

0

LVL 4

Expert Comment

YYYY-mm-dd is default date format of SQL Server so just convertion is not problem

now for calculate accurate age

Select UID, (datediff(mm,birthdate ,getdate())/12) as 'Year',(datediff(dd,birthdate,getdate())%365)-
( (datediff(dd,birhdate,getdate())/365)/4) as 'Days'

Note: datediff(dd,birhdate,getdate())/365)/4) this formula is used to substratct the days of Leap years
0

LVL 1

Author Closing Comment

Thanks
0

## Featured Post

### Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.