Get records of employees who joined last Month using DATEPART function in SQL Server

Last Reply on Feb 14, 2017 01:32 AM By AnandM

Posted on Feb 14, 2017 01:08 AM
emp_id name      sal      doj        address             mobile       DOB    pin              
1 Durgesh 25000 2013-05-03 Siddharthnagar 9823434025 1990-07-07 272207
2 Maneesh 30000 2010-02-05 Naugarh 9936521044 1993-05-01 110092
3 Priyanaka 55000 1980-10-10 Deoria 9984150368 1992-05-01 123465
4 Anand 35000 2017-01-01 Bihar 8826052014 1988-08-05 200113
5 Sanju 12000 2017-01-13 Delhi 9985212366 1985-02-05 257825
NULL NULL NULL NULL NULL NULL NULL NULL
             
               
               
I want those record who join in previos month              
               
Posted on Feb 14, 2017 01:32 AM

Hi telldurges,

Considering that you are trying to get the records of previous month based on current year so refer below query

SQL

DECLARE @Employee AS TABLE (emp_id INT,name VARCHAR(50),sal VARCHAR(50),doj DATETIME,[address] NVARCHAR(50),mobile VARCHAR(50),DOB DATETIME,pin VARCHAR(50))
INSERT INTO @Employee VALUES(1,'Durgesh',25000,'2013-05-03','Siddharthnagar',9823434025,'1990-07-07',272207)
INSERT INTO @Employee VALUES(2,'Maneesh',30000,'2010-02-05','Naugarh',9936521044,'1993-05-01',110092)
INSERT INTO @Employee VALUES(3,'Priyanaka',55000,'1980-10-10','Deoria',9984150368,'1992-05-01',123465)
INSERT INTO @Employee VALUES(4,'Anand',35000,'2017-01-01','Bihar',8826052014,'1988-08-05',200113)
INSERT INTO @Employee VALUES(5,'Sanju',12000,'2017-01-13','Delhi',9985212366,'1985-02-05',257825)

SELECT * FROM @Employee
WHERE DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,doj)
AND DATEPART(MONTH,DATEADD(MONTH,-1,GETDATE())) = DATEPART(MONTH,DATEADD(MONTH,0,doj))

Output

emp_id name sal doj address mobile DOB pin
4 Anand 35000 1/1/2017 Bihar 8826052014 8/5/1988 200113
5 Sanju 12000 1/13/2017 Delhi 9985212366 2/5/1985 257825