How to get the records of last month in SQL Server?-CodePudding

I want to get the records of last month based on my table [Sales], variable "SoldDate" in SQL Server.

For example, if today is 29/09/2021, I want to get the data for 01/08/2021-31/08/2021

The "SoldDate" variable is in the format 2018-04-11 00:00:00.000.

I would really appreciate your help! I have no idea how to get this to work :(

CodePudding user response：

You can use eomonth() :

SELECT DATEADD(DAY, 1, EOMONTH(SoldDate, -2)) AS StartDate, EOMONTH(SoldDate, -1) AS EndDate

CodePudding user response：

The best way to search through an indexed table is to calculate what date range you need, and filter on that. You can use DATEADD and EOMONTH for this. Note that since you have a time component, you cannot just use EOMONTH

SELECT *
FROM YourTable
WHERE SoldDate >= DATEADD(day, 1, EOMONTH(GETDATE(), -2))
  AND SoldDate <  DATEADD(day, 1, EOMONTH(GETDATE(), -1))

EOMONTH gives you the end of a month, the second parameter denotes how many months to shift.
Note correct use of the half-open interval >= AND <, this dealls correctly with the time component.