[SalesForce] SFMC Filter Activity – Date + –

When creating a filter in the Marketing Cloud, say when filtering a Data Extension, you can base any of the statements in the filter off of a date field. The question I have relates to when using the month as a parameter of the date statement.

Example: I want to run an email on the first of every month for people who made a purchase last month.

If I were to do: "OrderDate is before today AND OrderDate is Greater than Today – 1 Month,

Would it simply apply 30 days as a month or would it apply any logic based on the current date? For example, if it was June 1st, would it only go back as far as May 2nd, or would it know that May had 31 days so it needs to go back 31 days? Or does it pick up that its June so it only goes back 30 days? Or just by default 30 days?

I'm just looking for consistent behaviour that I can leave in an automation, so any help is appreciated. Thanks!

Best Answer

I've used it earlier and it calculates itself based on the days in the month. So, it does not default to 30 days and adjusts based on the days in the month (I believe it handles leap year as well). You can run a quick test to verify it before you automate it.

Unfortunately, this is not very well documented in SFMC guides.

Related Solutions

[SalesForce] How to determine age with AMPScript

I was able to write an block of AMPScript to accomplish this. It is not the most elegant, but it works. The key is to use the AMPScript functions DatePart, DateParse, DateAdd, & DateDiff to calculate some key dates between now and the original date of birth (or any past date for that matter).

%%[ 
var @days, @years, @months
var @dob, @lastBDay
var @dt1 /* now */
var @dt2 /* lastBDay + months */
var @dt2 /* now - (lastDay + months)  */

set @dt1 = Now()
set @dob = "7/4/1976"
set @lastBDay = DateParse(concat(Datepart(@dt1,"year"),"-",Datepart(@dob,"month"),"-",Datepart(@dob,"day")))
if (datediff(@lastBDay,@dt1,"D") < 0) then
    set @lastBDay = DateParse(concat(Datepart(DateAdd(@dt1,-1,"Y"),"year"),"-",Datepart(@dob,"month"),"-",Datepart(@dob,"day")))
endif

set @years = DateDiff(@dob,@dt1,"Y")
set @months = DateDiff(@lastBDay,@dt1,"M")
set @dt2 = DateAdd(@lastBDay,@months,"M")
set @days = DateDiff(@dt2,@dt1, "D")

]%%
<pre>
input date = %%=v(@dob)=%%

years = %%=v(@years)=%%
months = %%=v(@months)=%%
days = %%=v(@days)=%%
</pre>

output:

input date = 7/4/1976

years = 39
months = 1
days = 23

p.s. Here's an online site you can use to validate against. http://www.timeanddate.com/date/durationresult.html?m1=7&d1=4&y1=1976&m2=8&d2=27&y2=2015

[SalesForce] Calculating the last day of next month using Ampscript

You could do something like this:

%%[

set @twoMonthsDate = dateadd(now(), 2, "M")
set @month = DatePart(@twoMonthsDate, "M")
set @year = DatePart(@twoMonthsDate, "Y")
set @firstOfTwoMonthsDate = dateparse(concat(@month,"/01/",@year))
set @lastDayNextMonth = dateadd(@firstOfTwoMonthsDate,-1,"D")

]%%
<br>now(): %%=now()=%%
<br>@twoMonthsDate: %%=v(@twoMonthsDate)=%%
<br>@month: %%=v(@month)=%%
<br>@year: %%=v(@year)=%%
<br>@firstOfTwoMonthsDate: %%=v(@firstOfTwoMonthsDate)=%%
<br>@lastDayNextMonth: %%=v(@lastDayNextMonth)=%%

Output

now(): 5/31/2016 9:05:14 AM 
@twoMonthsDate: 7/31/2016 9:05:14 AM 
@month: 07 
@year: 2016 
@firstOfTwoMonthsDate: 7/1/2016 12:00:00 AM 
@lastDayNextMonth: 6/30/2016 12:00:00 AM

Best Answer

Related Solutions

[SalesForce] How to determine age with AMPScript

[SalesForce] Calculating the last day of next month using Ampscript

Related Topic