How To Find Employees Hired Last Month Or Year In SQL Server

In this blog, we will understand how we can find employees hired last month or year. We will understand DATEDIFF function in SQL server. The DATEDIFF function is used to calculate the difference between two dates.

Introduction

In this blog, we will understand how we can find employees hired last month or year. We will understand DATEDIFF function in SQL server. The DATEDIFF function is used to calculate the difference between two dates.

Syntax

DATEDIFF(expression1, expression2)

Here the data type of <expression1> and <expression2> is some type of date, time, or datetime. The result is <expression2> - <expression1>.

Step 1

Open SQL server 2014 and create a table of employees,

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Gender] [char](10) NULL,
    [Age] [int] NULL,
    [Position] [nvarchar](50) NULL,
    [Office] [nvarchar](50) NULL,
    [HireDate] [datetime] NULL,
    [Salary] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2

 

Insert some dummy data with employee record

insert into Employees values('Airi Satou','Male',34,'Software Engineer','Tokyo','2018-11-01',162700)
insert into Employees values('Angelica Ramos','Male',40,'Accountant','London','2018-08-10',1200000)
insert into Employees values('Caesar Vance','Male',42,'Sales Assistant','New York','2018-05-01',162700)
insert into Employees values('Cara Stevens','Male',30,'Pre-Sales Support','New York','2018-11-01',162700)
insert into Employees values('Colleen Hurst','Male',34,'Software Engineer','San Francisco','2018-12-05',86000)
insert into Employees values('Dai Rios','Male',45,'Regional Director','London','2018-08-10',132000)
insert into Employees values('Gavin Joyce','Female',28,'Developer','San Francisco','2018-09-15',206850)
insert into Employees values('Brenden Wagner','Male',38,'Support Engineer','New York','2018-10-20',372000)
insert into Employees values('Cara Stevens','Female',30,'Integration Specialist','Tokyo','2018-12-25',145600)
insert into Employees values('Cedric Kelly','Female',22,'Senior Javascript Developer','Edinburgh','2018-12-30',106450)

Query for employees who were hired in the last year,

select *, DATEDIFF(YEAR,HireDate, GETDATE()) as Different
from Employees
where DATEDIFF(YEAR,HireDate, GETDATE()) between 0 and 1
order by HireDate ASC

Query for employees who were hired in  the last months,

select *, DATEDIFF(MONTH,HireDate, GETDATE()) as Different
from Employees
where DATEDIFF(MONTH,HireDate, GETDATE()) between 0 and 1
order by HireDate DESC

 

 

Query for employees who were  hired in the last 30 days,

select *, DATEDIFF(DAY,HireDate, GETDATE()) as Different
from Employees
where DATEDIFF(DAY,HireDate, GETDATE()) between 0 and 30
order by HireDate ASC