sql server help fetch records

Question

hi I have one doubt in sql server
how to get more than month records in sql server
CREATE TABLE [dbo].[emp](
[empid] [int] NULL,
[doj] [date] NULL
)

INSERT [dbo].[emp] ([empid], [doj]) VALUES (1, CAST(N'2020-04-05' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (2, CAST(N'2021-11-09' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (3, CAST(N'2021-11-08' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (4, CAST(N'2021-10-04' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (5, CAST(N'2021-11-20' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (6, CAST(N'2021-11-07' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (7, CAST(N'2021-11-10' AS Date))
INSERT [dbo].[emp] ([empid], [doj]) VALUES (8, CAST(N'2021-12-03' AS Date))
base on above data I want output like below
empid |doj
1 |2020-04-05
4 |2021-10-04
2 |2021-11-09
7 |2021-11-10
I tried like below

SELECT [empid]
,[doj]
FROM [emp]
where datediff(mm,doj,getdate())>1
above query is missing one month one day and onemonth 2days records.

please tell me how to write query to achive this task in sql server

Answer 1

Hi，@harinathu
Welcome to Microsoft T-SQL Q&A Forum!
Please check this:

SELECT [empid],[doj],  
DATEADD(mm, -1, getdate()) as date1,  
DATEDIFF(mm,[doj],DATEADD(mm, -1, getdate())) as Month1,  
CASE WHEN doj <= DATEADD(mm, -1, getdate()) then 1  
ELSE 0 END AS Greatorequalthan1Month  
FROM [emp]  
  
SELECT [empid],[doj]  
FROM [emp]  
WHERE doj <= DATEADD(mm, -1, getdate())  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

where datediff(mm,doj,getdate())>1

The reason this does not work as you intended is that datediff counts boundary passages. So this returns 0:

SELECT datediff(MONTH, '20211201 00:00', '20211231 23:59')

whereas this query returns 0:

SELECT datediff(MONTH, '20211130 23:59', '20211201 00:01')

The solution is often to go up one level in granularity, for instance:

where datediff(DAY, doj ,getdate())> 30

But it is a little tricky if you want exact months. (Which is why Viorel has been asking you questions.)