SQL-获得所有具有1年或以上经验的员工

由 发布于 sqlsql-server
 收藏

I've been trying display all employees with their 1 or more year experience , but it keeps showing all , i'm using DATEDIFF(YEAR,Hired_Date,GETDATE()) >= 1 in where clause but even with less that a yr experience, it keeps showing.

员工人数

ID   Hired_Date
001  2018-05-01
002  2018-03-01
003  2020-05-01
004  2019-12-05
005  2017-03-01
评论
  • cnon
    cnon 回复
    DATEDIFF(YEAR,EmpStartDate ,GETDATE()) >= 1

    显示日历年的差异。因此2019-12-31和2020-01-01相隔1个日历年。

    Depending on how precise you need it to be, you should probably use month instead of year.

  • 俄对花儿笑
    俄对花儿笑 回复

    您的查询存在的问题是DATEDIFF给出了diff的天数，您可以将其除以365，将其转换为年。请执行以下查询：

    SELECT ID, Hired_Date WHERE (DATEDIFF(now(), hire_date))/365>=1;

  • 笑回首
    笑回首 回复

    In your Query you are using EmpStartDate instead of Hired_Date.

    select * from @T
where DATEDIFF(YEAR,Hired_Date ,GETDATE())>=1
  • 离别yi刻
    离别yi刻 回复
    SELECT *
FROM Employee_Tb
WHERE Hired_Date<CONVERT(DATE,DATEADD(year, -1, GETDATE()))