CODEWITHSUNDEEP
sqlsql-server
codeSeven
codeSeven

Reputation: 469

Get all employee with 1 year or more than experience

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 year experience, it keeps showing.

Employee_Tb

ID   Hired_Date
001  2018-05-01
002  2018-03-01
003  2020-05-01
004  2019-12-05
005  2017-03-01

Upvotes: 0

Views: 2754

Answers (4)

Nirav Akbari
Nirav Akbari

Reputation: 37

Please use the below query for SQL Server:

SELECT ID, Hired_Date
FROM Employee_Tb
WHERE (DATEDIFF(YEAR, Hired_Date, GETDATE())) >= 1;

If you want in MySQL you can use TIMESTAMPDIFF as below:

SELECT * FROM Employee_Tb WHERE TIMESTAMPDIFF(YEAR, Hired_Date, CURDATE()) >= 1;

TIMESTAMPDIFF if you want the difference in MONTH you can change the YEAR to MONTH or DAY whatever need.

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

SELECT *
FROM Employee_Tb
WHERE Hired_Date < CONVERT(DATE,DATEADD(year, -1, GETDATE()))

Upvotes: 3

kutschkem
kutschkem

Reputation: 8163

DATEDIFF(YEAR, EmpStartDate, GETDATE()) >= 1

shows the difference in calendar years. So 2019-12-31 and 2020-01-01 are 1 calendar year apart.

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

Upvotes: 0

Thiyagu
Thiyagu

Reputation: 1330

In your query you are using EmpStartDate instead of Hired_Date.

select *
from @T
where DATEDIFF(YEAR, Hired_Date, GETDATE()) >= 1

Upvotes: 0

Related Questions