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 Answers 2

3

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

2
  • 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())) May 16, 2018 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, 2018 at 13:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .