Hi Everyone,
We're looking to start running some triggered campaigns with Wordfly and have run into this issue a few times now. We're trying to create a list of people who's membership expires at the end of the month, or who have a performance on a certain day. But when we use the relative dates as criteria (DayRunDate, EnRunMonth), it seems to be looking at time as well. So it will only show people whose membership expires exactly at midnight at the end of the month. Does anyone else have this issue? Is there a way to make the relative date criteria just look at the date and ignore the time?
Thanks!
-George
You may need to do a little more investigation here. You can "Show Query" on your list criteria and see the actual SQL that is generated from the relative date settings you have on your list.
It may be interesting to note that for DayRunDate, depending on whether your criteria operator is > or <, the generated SQL expression will result in a comparison date that is either at midnight (00:00) on the selected day, or at 11:59:59.997 pm on the selected day (there are no .998 or .999 seconds in the SQL Server datetime data type).
EnRunMonth is a big more complicated, and I _believe_ we found a bug with that in a forum post from last year that may have affected 12.5, but I don't remember exactly what the behavior was. But the point here is that by design, these criteria are not supposed to be taking time into account, so you should investigate the actual query to see why you're getting the behavior that you're seeing.
Thanks Nick. I think the issue that we're having is that DayRunDate is using midnight and 11:59:59.997, but when I use the = operator, it still looks for those specific times. I guess I'm wondering if there is a way for it to ignore the time, so that when I say "= to DayRunDate", it will look for anything on that date, and not specifically on that date at 11:59:59.997. Does that make sense?
Ah, yes that makes sense. It’s not very common to use the = operator with relative dates; the data type is always [datetime], which is why you’re running into this issue.
You should be able to get the behavior you’re looking for by creating two criteria for that field, using >= and <= pointing to the same relative day. This essentially says “between 00:00 and 11:59 pm on DayRunDate”. You can’t actually use the between operator here because it doesn’t support relative dates, but it’s equivalent to using two separate criteria.
Gotchya. It's good to know that using the = operator isn't common practice. When I used the < and > it worked great, so we'll continue using that. Thanks!