SQL中的年龄计算 [英] Age calculation in SQL
问题描述
我有一个这样的查询,它会计算员工的出生年龄。
I had a query like this, which calculates the age for the employees with their date of birth.
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
问题是如何选择他的年龄> 40这里有条件。
可以帮助我。
The question is how to select he age > 40 where condition here.
can any one help me.
推荐答案
从员工中选择* DATEDIFF(HOUR,Employees.BirthDate,GetDate) ())/ 8766> 40
select * from Employees where DATEDIFF(HOUR, Employees.BirthDate, GetDate())/8766 > 40
您可以使用年间隔yy直接获得年龄
select * from Employees DATEDIFF(yy,Employees.BirthDate,GetDate())> ; = 40
希望它有所帮助。
You can get age directly by using year interval"yy"
select * from Employees where DATEDIFF(yy,Employees.BirthDate,GetDate()) >= 40
Hope it helps.
你想要的是使用 AGE 列在查询之后.....我知道(几个月前已经搜过很多)这是不可能的...
如果你想要保留结果暂时使用 CTE(公用表格表达式) ...
或者您必须编写案例场景..
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 ..
CASE
WHEN(月(GETDATE())* 100)+ DAY(GETDATE())> =(MONTH(Employees.BirthDate)* 100)+ DAY(Employees.BirthDate)
那么DATEDIFF(年,员工。 BirthDate,GETDATE())
ELSE DATEDIFF(年,员工,生日,GETDATE()) - 1
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
一次再次在 WHERE clouse ...
Once again within WHERE clouse ...
这篇关于SQL中的年龄计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!