Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-04
15
Medium Priority
?
559 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

971 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