Age calculation in SQL

Question

0.00/5 (No votes)

See more:

I had a query like this, which calculates the age for the employees with their date of birth.

SQL

SELECT
  CASE
     WHEN (MONTH(GETDATE()) * 100) + DAY(GETDATE()) >= (MONTH(Employees.BirthDate) * 100) +  DAY(Employees.BirthDate)
     THEN DATEDIFF(Year, Employees.BirthDate, GETDATE())
     ELSE DATEDIFF(Year, Employees.BirthDate, GETDATE())-1
  END AS 'Age' from Employees

The question is how to select he age > 40 where condition here.
can any one help me.

Posted 20-Jun-13 19:41pm

kesav prakash

Updated 20-Jun-13 19:50pm

Rajesh Anuhya

v2

Add a Solution

4 solutions

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)

Nick Ginis · Answer 1 · 2013-06-20T19:51:00

Solution 1

select * from Employees where DATEDIFF(HOUR, Employees.BirthDate, GetDate())/8766 > 40

Posted 20-Jun-13 19:51pm

Nick Ginis

Comments

Nripendra Ojha 21-Jun-13 2:36am

what is 8766 ?

Nick Ginis 21-Jun-13 3:54am

The number of hours in a year.

ArunRajendra · Answer 2 · 2013-06-20T20:10:00

Solution 2

You can get age directly by using year interval"yy"
select * from Employees where DATEDIFF(yy,Employees.BirthDate,GetDate()) >= 40

Hope it helps.

Posted 20-Jun-13 20:10pm

ArunRajendra

Updated 20-Jun-13 21:18pm

v2

Comments

Nick Ginis 21-Jun-13 2:14am

This won't get their age, just the difference in years. If their birthday hasn't occurred yet it will show them as 1 year older than they are.

ArunRajendra 21-Jun-13 2:36am

Yes, the age is not increment till the next birthday. So person will remain at the age of 40 and becomes 41 only on or after the birthday.

Moumit Mondal · Answer 3 · 2013-06-20T21:03:00

What you want is to use the AGE column with in the query after where ..... but i much i know (Already searched a lot few months ago) it's not possible ...

If you want to preserve the result for a while use CTE(Common Table Expression) ...

Or you have to write the Case scenario ..

Quote:
CASE
WHEN (MONTH(GETDATE()) * 100) + DAY(GETDATE()) >= (MONTH(Employees.BirthDate) * 100) + DAY(Employees.BirthDate)
THEN DATEDIFF(Year, Employees.BirthDate, GETDATE())
ELSE DATEDIFF(Year, Employees.BirthDate, GETDATE())-1

Once again within WHERE clouse ...

Aarti Meswania · Answer 4 · 2013-06-20T20:30:00

Solution 3

get age in sql
visit
Get Duration Between Two Dates in Years, Months, Days, and Hours using SQL Server[^]
Happy Coding!
:)

Posted 20-Jun-13 20:30pm

Aarti Meswania