?
Solved

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

Posted on 2011-03-04
15
Medium Priority
?
567 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

569 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