查询年龄大于50岁的员工姓名，精确到日

Question

Table:EmployeesName     BirthdateNancy 1948-12-08 00:00:00.000
Andrew 1952-02-19 00:00:00.000
Janet 1963-08-30 00:00:00.000
Margaret 1937-09-19 00:00:00.000
Steven 1955-03-04 00:00:00.000
Michael 1963-07-02 00:00:00.000
Robert 1960-05-29 00:00:00.000
Laura 1958-01-09 00:00:00.000
Anne 1966-01-27 00:00:00.000
SELECT DATEDIFF(year,birthdate,GETDATE()) AS Age
FROM Employees
WHERE DATEDIFF(year,birthdate,GETDATE())>50我的这种查询方法只能精确到年，而且写了两次DATEDIFF(year,birthdate,GETDATE()) ，如果能精简下？不用创建视图的方式。请高手指点，谢谢！

Answer 1

SELECT DATEDIFF(day,birthdate,GETDATE()) AS Age
FROM Employees
WHERE DATEDIFF(day,birthdate,GETDATE())>50

Answer 2

select
 *
from
 (
  select
   datediff(dd,birthday,getdate()) as age
  from
    employees
  )t
where
  age>=50
    

Answer 3

要精确的计算的话 需要这样
declare @s datetime
set  @s='1982-07-18'
SELECT
 年龄=case when
      datediff(day,dateadd(year,datediff(year,@s,getdate()),@s),getdate())>=0  
     then
      datediff(year,@s,getdate())
    else
      datediff(YY,@s,getdate())-1 end

Answer 4

SELECT DATEDIFF(day,birthdate,GETDATE()) AS Age
FROM Employees
WHERE DATEDIFF(day,birthdate,GETDATE())>50
year  month day ====

Answer 5

create table Employees(Name varchar(10),Birthdate datetime)
insert into Employees values('Nancy'   , '1948-12-08 00:00:00.000')
insert into Employees values('Andrew'  , '1952-02-19 00:00:00.000')
insert into Employees values('Janet'   , '1963-08-30 00:00:00.000')
insert into Employees values('Margaret', '1937-09-19 00:00:00.000')
insert into Employees values('Steven'  , '1955-03-04 00:00:00.000')
insert into Employees values('Michael' , '1963-07-02 00:00:00.000')
insert into Employees values('Robert'  , '1960-05-29 00:00:00.000')
insert into Employees values('Laura'   , '1958-01-09 00:00:00.000')
insert into Employees values('Anne'    , '1966-01-27 00:00:00.000')
insert into Employees values('Anne'    , '1960-05-02 00:00:00.000')
insert into Employees values('Anne'    , '1960-05-03 00:00:00.000')
insert into Employees values('Anne'    , '1960-05-01 00:00:00.000')
goselect * 
from Employees
where datediff(yy,Birthdate,getdate()) > 50 or 
(datediff(yy,Birthdate,getdate()) = 50 and right(convert(varchar(10),Birthdate,120),5) <= right(convert(varchar(10),getdate(),120),5))drop table Employees/*
Name       Birthdate                                              
---------- ------------------------------------------------------ 
Nancy      1948-12-08 00:00:00.000
Andrew     1952-02-19 00:00:00.000
Margaret   1937-09-19 00:00:00.000
Steven     1955-03-04 00:00:00.000
Laura      1958-01-09 00:00:00.000
Anne       1960-05-02 00:00:00.000
Anne       1960-05-01 00:00:00.000（所影响的行数为 7 行）*/

Answer 6

Select * 
from Employees
where Birthdate< Dateadd(yy,-50,getdate()) 

Answer 7

SELECT DATEDIFF(year,birthdate,GETDATE()) AS Age
FROM Employees
WHERE (convert(int, convert(char(8), getdate, 112))
 - convert(int, convert(char(8), birthdate, 112)))/10000 > 50