Extract and Format Dates and Times

These functions provide tools for interpreting date values. Many of these functions process and return the information as a date which can be compared to other date and time values. Many of these functions require values in date or time zone format as parameters. The parameters are generated by nesting the functions ecm:toDate and ecm:timeZone.

Modifications to the extracted values are made for the purposes of the query only. The actual data stored in the target source is not changed in any way.

Function Overview

​ecx:formatDate​

The function ecx:formatDate formats a date according to the specified pattern. The value returned is a string data type.

Example

The following example takes a given date and time in Central European Time (UTC+01:00), applies a specific date formatting and changes the time zone to Greenwich Mean Time (UTC+00:00). The final parameter displays the time zone.

${ecx:formatDate(ecm:toDate('2010-03-27 16:12:21', ecm:timeZone('Europe/Warsaw')), 'EEEE, MMMMM d, hh:mm aaa', ecm:timeZone('Europe/London'), 'en', true)}

The output is displayed as ​Saturday, March 27, 03:12 PM GMT​.

Structure​

ecx:formatDate(date, string, timezone, String, Boolean)

Example:

${ecx:formatDate(date.today,'yyyy-MM-dd',ecm:timeZone('Europe/Berlin'),'de',false)}

Parameters​

ecx:fmtDateInTimeZone​

The function ecx:fmtDateInTimeZone interprets a given date in the specified source time zone. It then sets the specified target time zone on the date but retains the date and time fields as they were on the original date. The date is formatted as a string with the given pattern and locale. The value returned is a string data type.

Example

The purchase date is stored in the custom attribute user.CustomAttribute['PurchaseDate']​. The function to convert this value to Sydney time is constructed as follows:

${ecx:fmtDateInTimezone(user.CustomAttribute['PurchaseDate'], null, 'Australia/Sydney', 'yyyy-MM-dd HH:mm:ssZ', 'en')}

If the attribute value is 2013-09-08T02:00:00+0200 (midnight at UTC time), the output is ​2013-09-08T00:00:00+1000.

Structure​

ecx:fmtDateInTimeZone(date, string, string, string, string)

Parameters​

ecx:formatTimestamp​ ​

The function ecx:formatTimestamp returns a timestamp in the desired string format. The value returned is a string data type.

Example

Apply a specific date format to a timestamp and change the time zone to Central European Time (UTC+10:00).

${ecx:formatTimestamp('1272381141000', 'EEEE, MMMMM d, hh:mm aaa', ecm:timeZone('Europe/Berlin'), 'en', true)}

The output is displayed as ​Tuesday, April 27, 05:12 PM CEST.

Structure​

ecx:formatTimestamp(long, timezone, String, Boolean)

Parameters​

​ecx:getDate​

The function ecx:getDate returns the current date in the format 'yyyyMMdd' with a specified time zone. The value returned is a string data type.

Example

${ecx:getDate(timezone.europeBerlin)}

Structure​

ecx:getDate(timezone)

Parameters​

​ecx:getTime​

The function ecx:getTime returns the current time in the format 'HHmmss" with the specified time zone. The value returned is a string data type.

Example

${ecx:getTime(timezone.europeLondon)}

Structure​

ecx:getTime(timezone)

Parameters​

​ecx:getDateTime​

The function ecx:getDateTime returns the current date and time in the format 'yyyyMMddHHmmss' with a specified time zone. The value returned is a string data type.

Example

${ecx:getDateTime(timezone.pacificFiji)}

Structure​

ecx:getDateTime(timezone)

Parameters​

​ecm:addInterval​

The function ecm:addInterval adds (or subtracts) an interval of time to the specified date. The value returned is a date data type.

Example

A message is sent to all customers who made a purchase in the last five days. The message contains a list of the products the customer purchased during that time.

All customer purchases are saved in a related data set ​Purchases​. The related data set is linked to the attribute ​Client_ID​. The client ID saved in the attribute is identical to the key of the related data set. Since customers can make multiple purchases, the related data set is not unique.

The ecm:addInterval function returns the value of today's date minus five days:

ecx:formatDate(ecm:addInterval(date.today, '-5d'),'yyyy-MM-dd',ecm:timeZone('Europe/London'),'',false

The value returned by this function is then used as a parameter in the ecx:filter function, which returns all entries for purchases made in the last five days. Since the related data set is not unique, a ​ForEach loop inserts the data into the message. The entire expression, set inside the ​ForEach​ loop, is constructed as follows:

<%ForEach var="count" items="${ecx:filter(ecx:related('Purchases', user.CustomAttribute['client_ID']), ['Date'], '>=', ecx:formatDate(ecm:addInterval(date.today, '-5d'),'yyyy-MM-dd',ecm:timeZone('Europe/London'),'',false))}"%> <%${count.Article}%> <%/ForEach%>

Structure​

ecm:addInterval(date, string)

Parameters​

The value used as the date parameter must include the interval specified by the string parameter.  For example, to subtract 48 hours from the target date, the value used as the date parameter must include the hour

​ecm:day​

The function ecm:day returns the day component of the referenced date. The first day of the month has a value of 1. The value returned is an integer data type.

Example

A specific paragraph is entered if the recipient's date of birth equals the date of sendout.

The content is inserted into the message with an ​InsertIf​ statement, which is constructed as follows:

<%InsertIf expression="${(ecm:day(user['DateOfBirth'])==ecm:day(date.Today))and(ecm:month(user['DateOfBirth'])==ecm:month(date.Today))}"%> Text <%/InsertIf%>

The parameter ​date.Today​ returns the full date (day, month, and year). The function ecm:day extracts the day part from both the user attribute and the date of sendout. The results returned from both functions are compared using the operator ​==​.

Note that the function ecm:month is used in a similar fashion to check the month part of the date.

Structure​

ecm:day(date)

Parameters​

​ecm:month​

The function ecm:month returns the month component of the referenced date. January has a value of 1. The value returned is an integer data type.

Example

A specific paragraph is entered if the recipient's date of birth equals the date of sendout.

The content is inserted into the message with an ​InsertIf​ statement, which is constructed as follows:

<%InsertIf expression="${(ecm:day(user['DateOfBirth'])==ecm:day(date.Today))and(ecm:month(user['DateOfBirth'])==ecm:month(date.Today))}"%> Text <%/InsertIf%>

The parameter date.Today returns the full date (day, month, and year). The function ecm:month extracts the month part from both the user attribute and the date of sendout. The results returned from both functions are compared using the operator ​==.

Note that the function ecm:day is used in a similar fashion to check the day part of the date.

Structure​

ecm:month(date)

Parameters​

​ecm:year​

The function ecm:year returns the year component of the referenced date. The value returned is an integer data type.

Example

The date of each recipient's high school graduation is stored as a group member attribute. The year part of this date is extracted for use in a message. The expression for inserting this information in a message is constructed as follows:

<%${ecm:year(ecm:toDate(user.MemberAttribute['graduation_date'], ''))}%>

Note that the function ecm:toDate is also used to ensure that the value stored in the member attribute is formatted as a date data type to match the type required for the parameter.

Structure​

ecm:year(date)

Parameters​

​ecm:timeZone​

The function ecm:timeZone converts the specified time zone ID to a (Java utility class) timezone. The value returned is a timezone type.

This function is often used to convert values to timezones for use with functions that require timezone types in parameters.

Example

A message is sent to all customers who made a purchase in the last 5 days. The ecm:addInterval function returns the value of today's date minus five days:

ecx:formatDate(ecm:addInterval(date.today, '-5d'),'yyyy-MM-dd',ecm:timeZone('Europe/London'),'',false.

The ecm:timeZone function determines which time zone is used when calculating the value of today's date (date.today).

Structure​

ecm:timeZone(string)

Parameters​

​ecm:trunc​

The function ecm:trunc returns a new date where the specified time or date component is reset to a default value (truncated). The value returned is a date data type.

Example

A selection is used to send a thank you message to group members on the anniversary of the date they signed up for a newsletter. This date is stored in the custom attribute ​WelcomeDate​.

The function ecm:trunc is used to reset the time component for both the current date and the attribute value to zero. It is then possible to use the function ecm:equal to define the selection criteria because the time component is negated. The expression for use in the selection is constructed as follows:

${ecm:equal(ecm:trunc(user.CustomAttribute['WelcomeDate'], 'time')), (ecm:trunc(date.Today, 'time'))}

Structure​

ecm:trunc(date, string)

Parameters​