Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-04
15
Medium Priority
?
552 Views
Last Modified: 2012-08-13
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
Comment
Question by:Steynsk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +4
15 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35037270
select UID, birthdate, datediff(year,birthdate,getdate())
from people
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35037284
scratch that, it doesn't take the month into account... stand by
0
 
LVL 5

Expert Comment

by:jijeesh
ID: 35037307
You may use type convert for converting your varchar to datetime

select datediff(yy, convert(datetime,birthdate), getdate())
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35037317
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

by:derekkromm
ID: 35037319
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

by:jijeesh
ID: 35037359
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

by:knightEknight
ID: 35037445
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

by:Thomasian
ID: 35037480
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

Open in new window

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35037529
-- 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

by:Steynsk
ID: 35037700
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

by:derekkromm
ID: 35037722
my UDF is accurate to the day
0
 
LVL 1

Author Comment

by:Steynsk
ID: 35037763
Hi Derek,

Sorry I get:

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

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 35037835
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

by:samijsr
ID: 35042987
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

by:Steynsk
ID: 35067177
Thanks
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question