Click here to Skip to main content
14,734,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table that is updated daily and I have column called Year (that when someone was born) that I would like to calculate the age of the person.

USERID  Vendors  dob(Year)   login
1        yes     1985        yes
2        yes     1961        yes


I would like to have something like this

USERID  Vendors  dob(Year)   login   Age
1        yes     1985        yes     34
2        yes     1961        yes     59


What I have tried:

SELECT 
       dob(Year)  
        ,GETDATE() AS (Today)
        ,DATEDIFF (YY, dob(Year),GETDATE()) - 
  CASE
  WHEN DATEADD(YY,DATEDIFF(YY,dob(Year),GETDATE()), dob(Year)
       > GETDATE () THEN 1

  ELSE 0
       END  AS (AGE)
from table
Posted
Updated 30-Mar-20 21:27pm
v2
Comments
CHill60 31-Mar-20 5:58am
   
Using that logic you will overstate someone's age depending on which date you run the query. For example someone born in November 1980 is currently 39 but your logic will have return their age as 40

1 solution

Way over-complicated.

SQL Server has a Year() function which will return the year portion of the DateTime that it encases
SELECT CurrentYear = Year(GetDate())

For your query... this should give you a good start
SELECT *, Age = Year(GetDate()) - [dob(year)];
Reference:
YEAR (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
Maciej Los 31-Mar-20 2:27am
   
Excellent!
MadMyche 31-Mar-20 7:39am
   
thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900