Solved

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

Posted on 2011-03-04
15
506 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 42
Need to update TableA to TableB 6 34
1 FROM DUAL wont work with additional columns ?? 4 26
SQL Server Update Query Streamline 4 14
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now