1

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())
2

@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()))
0

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!

  • 3
    Your query will produce incorrect results, as it's not taking year into account. You need to add this to where clause: DATEPART(Y,sd.DateOfPurchase) = DATEPART(Y,DATEADD(M, -1, GETDATE())) – Mateusz Bartkowiak May 16 '18 at 15:25
  • @MBB 's was correct. I don't know how to accept the comment as the answer on here though.SELECT sd.FirstName, sd.LastName, sd.DateOfPurchase FROM StartingDE sd WHERE DATEPART(Y,sd.DateOfPurchase) = DATEPART(Y,DATEADD(M, -1, GETDATE())) – Harley May 17 '18 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.