Get Previous Month from DE using SQL

Question

I'm trying to pull things from the previous month (April only) from a data extension, but am running into something weird. Why wouldn't the initial query of "= DATEADD(month, -1, GETDATE())" produce the result I want? Is there a way around it in Marketing Cloud?

Here are the details

1. My two DE's are: StartingDE and EndingDE.
2. Columns for both DE's are the same.

3. Neither are sendable DE's, so I don't have a primary key.

4. Data for StartingDE is below:

   FirstName  |  LastName  |   DateOfPurchase
      Joe     |   Smith    |   Friday, May 11, 2018 12:00AM
      Patty   |   Page     |   Tuesday, April 17, 2018 12:00AM
      Kit     |   McMaster |   Wednesday, March 07, 2018 12:00AM
   

The below query returns 0 records:

SELECT sd.FirstName, sd.LastName, sd.DateOfPurchase
FROM StartingDE sd
WHERE sd.DateOfPurchase = DATEADD(month, -1, GETDATE())

The below query returns 2 records - Joe Smith (month of May) and Patty Page (month of April):

SELECT sd.FirstName, sd.LastName, sd.DateOfPurchase
FROM StartingDE sd
WHERE sd.DateOfPurchase >= DATEADD(month, -1, GETDATE())

Answer 1

@Harley - Correct query needs to take both Month and Year, not just one of them:

SELECT 
sd.FirstName, sd.LastName, sd.DateOfPurchase 

FROM StartingDE sd 

WHERE 
DATEPART(M,sd.DateOfPurchase) = DATEPART(M,DATEADD(M, -1, GETDATE()))
AND DATEPART(Y,sd.DateOfPurchase) = DATEPART(Y,DATEADD(M, -1, GETDATE()))

Answer 2

The issue with first query is that you are trying to equate sd.DateOfPurchase and DATEADD(month, -1, GETDATE()). It won't equate as whole date won't match the date that you have put in your DE. Time will be different.

Use this syntax:

SELECT sd.FirstName, sd.LastName, sd.DateOfPurchase FROM StartingDE sd WHERE DATEPART(M,sd.DateOfPurchase) = DATEPART(M,DATEADD(M, -1, GETDATE()))

DATEPART function will only give the part of date you want to match, in your case it is month.

Apply the same logic for your second query. Hope it helps!