Calculate age of user based on column in SQL

Question

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:

SQL

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 30-Mar-20 11:49am

learning_new

Updated 30-Mar-20 20:27pm

Maciej Los

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MadMyche · Accepted Answer · 2020-03-30T13:49:00

Solution 1

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

SQL

SELECT *, Age = Year(GetDate()) - [dob(year)];

Reference:
YEAR (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 30-Mar-20 13:49pm

MadMyche

Comments

Maciej Los 31-Mar-20 2:27am

Excellent!

MadMyche 31-Mar-20 7:39am

thank you