0

I want to count the amount of years from a the year the person is born to today. I have the components to use but dont know how to use them. 

SELECT COLUMN1, COLUMN2, DATEPART(YY,GETDATE()),
CONVERT(INT,+19)))LEFT(TABLE.COLUMN,2)

I want to use the +19 to show it before the birtyear. Example in the database the birthyear is showed as YY not YYYY. That is why I want to add the 19, so the SQL will count years from 19YY to 2013.

SterlinkArcher
  • 673
  • 3
  • 21
  • 36
  • Looks like possible duplicate. Please have a look [here][1] [1]: http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate – Scorpion Sep 03 '13 at 09:44
  • 2
    I don't exactly understand what you are asking. Do you want to display the year of birth, which is stored YY in a string, as YYYY? Or do you want to calculate years from year of birth till today? – Thorsten Kettner Sep 03 '13 at 09:48
  • Yes! Exactly @ThorstenKettner – SterlinkArcher Sep 03 '13 at 09:51
  • 1
    Since Day/Month is missing and birthdates after 31.12.1999 won't work, as comment `Select DatePart(YY,GETDATE()) - Cast('19' + LEFT(Col,2) as int) as PossibleAge ` – bummi Sep 03 '13 at 10:09
  • 2
    @andrejcurcic - Answering an "either/or" question with "Yes" doesn't clarify anything. – Martin Smith Sep 03 '13 at 11:36
  • @Scorpion that answer will not give an exact answer anyway. – t-clausen.dk Sep 03 '13 at 13:54

2 Answers2

4

Try this

SELECT  id
    ,   Name
    ,   DATEDIFF(yy, CONVERT(DATETIME, DOB), GETDATE()) AS AGE
    ,   DOB
FROM MyTable
Devart
  • 119,203
  • 23
  • 166
  • 186
Swapnil Ganjegaonkar
  • 116
  • 7
  • 1
    this will not find the age, according to this, when DOB is 2012-12-31 and getdate is 2013-01-01 that child will be 1 year old – t-clausen.dk Sep 03 '13 at 13:49
0
declare @DOB date = '19680411'
select datediff(year, @DOB, getdate())- case when month(@DOB)*32 + day(@DOB) > 
month(getdate()) * 32 + day(getdate()) then 1 else 0 end
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92