Solved

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

Posted on 2011-03-04
15
516 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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