15,741,947 members
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
Updated 20-Jun-13 19:50pm
v2

## Solution 1

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

Nripendra Ojha 21-Jun-13 2:36am
what is 8766 ?
Nick Ginis 21-Jun-13 3:54am
The number of hours in a year.

## 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.

v2
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.

## Solution 4

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 ...

kesav prakash 21-Jun-13 3:09am
k thanks i got my solution...