"In Past Before" Qualifier Issues in Smartlists

[Joey_Forcelli1]

I ran into an issue the other day and I am wondering if anyone else in the community has encountered this issue.

I created a smartlist to pull all leads where [date field] was in past before 6 months, that was the only criteria.  The number of leads that qualified for the smartlist was way more than it should have be so I knew something was wrong.

When I took a closer look at the leads I realized that not only did Marketo return leads where [date field] was in past before 6 months but it also returned leads where [date field] was empty.  I went back to the smartlist to add the additional qualifier of [date field] is not blank and that returned the correct leads.

Has anyone else experienced this?  It's not a huge deal but it is just annoying to have to add the additional qualifier for each use of "in past before".

JF

[Grégoire_Miche2]

Hi Joseph,

interesting...

Have you logged a support case? It is probably worth it and I would be interested to know what their answer is.

-Greg

[Joey_Forcelli1]

I have not yet but I plan on doing so.  I will keep you posted on what support says. 

[Devraj_Grewal · Accepted solution]

Joseph,

I have run into this issue in the past and opened a Support ticket. I remember the response stating that leads with no value for the date field will qualify if you attempt to query "in past before". So just have to remember to also bring in the filter of "date field is not empty".

[SanfordWhiteman]

N.B. It's not totally surprising, since in the database world, unless blank values are specifically​ excluded they may be coerced to "time zero." Depending on the time system used, this can be either 1970-01-01 00:00:00 or 1000-01-01 00:00:00 (with lots of other interesting quirks such as negative epoch time). Or blank values may be technically NULL dates, but may still be sorted either below or above all other dates, as opposed to excluded automatically;