We are going to work with Employee Data as shown below -
You can create this table with the below SQL code
CREATE TABLE [dbo].[Emp](
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[HireDate] [date] NULL );
You can Insert Data into this table using the below SQL code
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Alice', 'Ciccu','2021-01-07');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Paula', 'Barreto de Mattos','2021-01-06');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Alejandro', 'McGuel','2020-12-06');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Kendall', 'Keil', '2020-11-05');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Ivo', 'Salmre','2020-10-04');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Paul', 'Komosinski','2020-08-04');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Ashvini', 'Sharma','2020-07-04');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Zheng', 'Mu','2020-04-03');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Stuart', 'Munson','2019-11-02');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('Greg', 'Alderson','2019-10-02');
INSERT INTO [dbo].[Emp]
([FirstName],[LastName],[HireDate]) VALUES ('David', 'Johnson','2019-01-02');
To find employees hired in the last n months, we are going to use the DateDiff function to find the difference in Months between the current date and Hire Date.
Syntax - DATEDIFF ( datepart , startdate , enddate )
Lets suppose, we need to find employees hired in last 3 months.
Please see SQL Query below -
Select * from dbo.Emp
WHERE DATEDIFF(Month,HireDate,GetDate()) <= 3
For detailed video with explanation, please see the below YouTube video -
SQL - Find employees Hired in last n months using Datediff
👍👍👍👍👍👍👍
ReplyDelete