[SalesForce] Get Previous Month from DE using SQL

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

My two DE's are: StartingDE and EndingDE.
Columns for both DE's are the same.
Neither are sendable DE's, so I don't have a primary key.

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

Best Answer

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

Related Solutions

[SalesForce] SFMC query help- removing carriage returns from data extension

REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn*

In the above form and in every line of your code, the above has an unmatched parens. There's an extra parens at the end of the above line.

I don't know anything about SFMC, but you appear to have an extra parentheses in each line of your code (the very last one). I recommend you try removing it to see if it fixes your issue.

EDIT:

My "gut" is telling me to suggest trying something like this:

REPLACE((REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_') CLIENTID AND 
(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_') SENDID AND
(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_') SUBSCRIBERKEY AND
(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_') EMAIL AND
(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_') LISTID AND
(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_') EVENTDATE AND
(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_') EVENTTYPE AND
(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_') BOUNCECATEGORY AND
(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_') SMTPCODE AND
(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_') BOUNCEREASON AND
(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_') BATCHID AND
(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_') TRIGGEREDSENDCUSTOMERKEY AND
(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_') PROMOID)

The alternative being to also try removing the fieldname after the last parens (or even doing both the above and removing it after). For some reason, that seems unnecessary to me, but again, I'm not familiar with their code and haven't seen anything you may not have included in your post. It seems odd there would be nested REPLACE statements like that followed with the fieldname, first with options and then later without with more ANDs tacked on. It seems like there's potential to only need one outer replace statement. Its at least worth trying.

[SalesForce] Selecting Anniversary/Birthday in next month SQL

Why not try a DatePart with the DateAdd?

SELECT * FROM [Data Extension] WHERE
DATEPART(mm,birthday) = DATEPART(mm,(DateAdd(m,1,GetDate()))

This will take the recipient's birthday month and match it to the following month

Related Topic