To break a date out a date into component parts you need the actual date field and somewhere to receive the results of the breakout.

The example below uses the field Eval_expires shown on the layout below with the HTML underneath

Mark the field with the data in it as a Trigger field - see next screen shot

To create a trigger that will break out the data go to Site Settings | Misc settings | Advanced Field functions | Field Trigger Profiles

Creating Triggers

Create a new profile as shown here and save it

Having saved the profile re-open it and complete as shown below so that WiredContact knows which values to place into what field when the "trigger" runs

Supporting detail

In Help you may find Field rigger Profiles of value

date breakout [parameter list]

This function will take the value in a date field, and break out the year, month, day and day of week values and put those in separate fields. Year, Month, Day and Day Of Week can go into Integer fields, and Day Of Week and Month can go into String fields. You need to specify one (or more) of six destination fields for year, month, day, day of week(integer), day of week(string) and month (string). The syntax would be:

date breakout field="[field name]" table="[table name]" limits="(month is null) or (month = 0)" dayfield="[day field]" monthfield="[month field]" yearfield="[year field]" dayofweekfield="[day of week field]" dayofweeklongfield="[day of week long/string field]" monthlongfield="[month long/string field]"

The long/string field values are taken from the long day/long month in the locale settings.

To have a Query that locates the correct record for a given date you need to construct your query so that you’re looking for the days/months and not the years. So you would be using something like:

To achieve this

Select contact, uniqueid, EmailCampaign from wce_contact where (dripfeed like '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evaldayenglish = 27) and (evalmonth = 4))

Within the Site Notification Profile this looks like

Select contact, uniqueid, EmailCampaign from wce_contact where (dripfeed like '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evalmonth = <#date offset=30 format=M>) and (evaldayenglish = <#date offset=30 format=D>))

Select contact, uniqueid, EmailCampaign from wce_contact where (status like '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evalmonth = <#date offset=30 format=M>) and (evalday = <#date offset=30 format=D>))

For an eval date of April 27^th, the above query would look like:

((evalmonth = 4) and (evalday = 27))

You’ll need to make sure your SQL formatting is correct so you use quotes when it’s a text field, and you don’t use quotes when it’s not a text field. Used in this way, you don’t have to worry about the number of days in a month. The above is looking for an expiration that is +30 days from today. If you’re using the date in a different way, you’ll have to adjust the SQL.

If you wanted to make sure you covered a date range, then you would need something like:

Select contact, uniqueid, EmailCampaign from wce_contact where (status like '%999999%') AND ((Not Hold = 'Y') or (hold is null)) AND ((evaldate >= <#date offset=25 format=sql>) and (evaldate <= <#date offset=35 format=sql)) and

((evalmonth >= <#date offset=25 mask=M>) and (evalmonth <= <#date offset=35 mask=M>)) and

((evalday >= <#date offset=25 mask=D>) and (evalday <= <#date offset=35 mask=D>))

Because of the date range, if the range goes across a month, you’ll have possibly 2 months (4 and 5 for example) with multiple days (25 through 4 for example) and you’ll need to make sure that you exclude the days that don’t belong to the correct month (in this case – if the date is in month=4 day=27, you don’t want month=5 day=27 to hit, but you do want month=5 day=3 to hit) so you need to add the complete date test to make sure you’re in the correct span.

Article ID: 636, Created: March 27, 2014 at 5:04 PM, Modified: January 18, 2017 at 10:45 AM

DateBreak out

Add Feedback

Share this article