Select data that will expire in next 30 days in SQL server

pandeygolu4200
 
on Jun 30, 2022 05:12 AM
275 Views

I have table containing ExiprationDate, i need list from table which is going to expire in next 30 days.

LicenseId	ProviderId	DocumentType	LiecenseType	LicenseNumber	State	StartDate	ERADate	FileName	CreatedBy	CreatedOn	UpdatedOn	UpdatedBy	DeletedOn	DeletedBy	IsDelete
0B7763BA-FD87-4E55-A179-72372E295ADA	B4C413C7-94C7-4DC4-AF1D-F254A97BE918	227F2D73-565B-4C37-8202-86B88C3BA0EF	Driving	987654569	10	2011-06-26 02:50:44.220	2022-07-30 02:50:44.220	81 post Advt. (Eng.).fa6850fb-8c92-4122-9b05-6aa83	9B31094C-1AD6-43A2-9DBD-6A67F0AFE838	2022-06-30 09:30:25.937	NULL	NULL	NULL	NULL	0
81BA9469-1048-424A-9CE4-CAD16740C1A0	7BEE52F3-4538-45EE-A486-ADBB59A8B3CA	0E8294A6-4CEC-44E6-AD86-CDAB074440FD	Driving	987654569	10	2011-06-26 02:50:44.220	2022-07-30 02:50:44.220	81 post Advt. (Eng.).fa6850fb-8c92-4122-9b05-6aa83	9B31094C-1AD6-43A2-9DBD-6A67F0AFE838	2022-06-30 09:29:52.540	NULL	NULL	NULL	NULL	0
863FE7E5-3DD1-4253-854A-952AE2682DE9	7477165F-E0F1-416D-B443-BFF3D98C2B30	29DCB53A-3010-43AE-8154-8015C000D816	Driving	987654569	10	2011-06-26 02:50:44.220	2022-07-30 02:50:44.220	81 post Advt. (Eng.).fa6850fb-8c92-4122-9b05-6aa83	9B31094C-1AD6-43A2-9DBD-6A67F0AFE838	2022-06-30 09:28:43.857	NULL	NULL	NULL	NULL	0

here it is returning wrong list.

SELECT 
 [LicenseId]
,ISNULL(tuser.FirstName,'') ++ ' ' + ISNULL(tuser.LastName,'') as ProviderName
,tbldoc.DocumentName AS [DocumentType]
,[ERADate] AS ExpirationDate
,[FileName] AS Document
,tblpro.[ProviderId]
 FROM [dbo].[tblProviderDocumentInfo] tblpro WITH(NOLOCK)
 INNER JOIN tblProviderPersonalInformation TPL  ON tblpro.[ProviderId]=TPL.[ProviderId]
 INNER JOIN tblUsers tuser  ON TPL.[UserId]=tuser.[UserId]
 INNER join tblDocument tbldoc on tblpro.DocumentType=tbldoc.DocumentId
 WHERE DATEDIFF(DAY,GETUTCDATE(),[ERADate])<=30
 ORDER BY  tblpro.[CreatedOn] 

My Above query for getting list for 30 days

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 30, 2022 05:48 AM
pandeygolu4200 says:
WHERE DATEDIFF(DAY,GETUTCDATE(),[ERADate])<=30

Use BETWEEN operator.

Replace with below code.

WHERE [ERADate] BETWEEN GETUTCDATE() AND DATEADD(DAY,30,GETUTCDATE())