Hey everyone,
I feel very silly for asking this question, as it seems so simple, but here we go.
I would like to be able to set up an extraction that can be run once a month that always grabs constituents whose memberships expire next month. I thought I could do this by setting up two criteria in the extraction:
Membership Exp Date >= BRunMonths +1
Membership Exp Date <= ERunMonths +1
I expected that when I ran this extraction today (in November), that this would grab all memberships that expire between December 1, and December 31. However, what I'm finding is that it only pulls memberships that expire between December 1- December 30th. I suppose that's because November only has 30 days, so what the query is doing is taking 11/1 and 11/30 and changing them to 12/1 and 12/30. But this is definitely not what I want.
Can anyone confirm that this is what this criteria does (so that I can stop trying to make this work and find a different solution)?
You found a bug!
And your analysis is 100% correct -- if you were to run this query in February, you'd similarly only catch until March 28th.
Do submit this to the network to be fixed, but in the meantime, you can correct the issue by manual-editing your query SQL.
Based on what you've described, you should have a segment of your query that looks EXACTLY like this:
DateAdd(mm,1,DateAdd(ss,-1,dateadd(mm,1,convert(datetime,DateName(mm,GetDate())+' 1 '+DateName(yy,GetDate())))))
Change it to the following:
DateAdd(ss,-1,DateAdd(mm,1,dateadd(mm,1,convert(datetime,DateName(mm,GetDate())+' 1 '+DateName(yy,GetDate())))))
Thanks Nick! I checked the query, and you were right! It contained this:
which I made your suggested edits to. Generated the counts again, checked the segment contents, and got my desired result. Thank you! I'll report this bug to Tessitura.