Dates in Adobe Campaign cross three runtimes — JavaScript, XML, and SQL — and each has its own format. This reference covers every conversion pattern and the built-in helpers that make them reliable in production.

For example, a developer needs to convert dates between different formats in Adobe Campaign workflows and ensure consistency across the platform.

Preparation

Current date

getCurrentDate(); // Date of the database server — use this in production
// new Date()     // Date of the Linux application server — deprecated, avoid

Always prefer 📕 getCurrentDate over new Date() when server timezone consistency matters.

Walkthrough

JavaScript Date → String

Full reference available in 📕 formatDate.

formatDate(getCurrentDate(), "%4Y%2M%2D-%2H%2N%2S"); // "20190430-150837" — folder names, filenames
formatDate(getCurrentDate(), "%A %D %B %4Y"); // "Tue 30 Apr 2019" — technical emails
formatDate(getCurrentDate(), "%Al %D %Bl %4Y"); // "Tuesday 30 April 2019" — marketing emails
formatDate(getCurrentDate(), "%4Y%2M"); // "202005" — delivery labels

Also valid in workflow activity configuration fields (e.g. file path):

/incoming/<%= formatDate(getCurrentDate(), '%4Y%2M%2D-%2H%2N%2S') %>-file.txt

XML Date → JavaScript Date

Full reference available in 📕 parseTimeStamp.

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "nms:recipient",
    operation: "get",
    select: { node: { expr: "@lastModified" } },
    where: { condition: { expr: "@id=123456" } },
  },
});
var recipientXml = query.ExecuteQuery();
var lastModified = parseTimeStamp(recipientXml.$lastModified); // Date

Note: SQL query reference available at 📕 queryDef

parseTimeStamp accepts all ISO 8601 variants:

2005-03-02 00:00:00
2005-03-02
2005-03-02T14:32:26
2005-03-02T14:32:26.0000
2005-03-02T14:32:26.0000000+01:00
2005-03-02T14:32:26+01:00
2005-03-02T14:32:26Z
2005-03-02T14:32:26.0005Z
2005/03/02 14:32:25

JavaScript Date → SQL String

When writing a date back to the database via NLWS, use Format.formatDateTimeInter:

loadLibrary("xtk:common.js"); // required for Format
var recipient = NLWS.nmsRecipient.load(123456); // load recipient with id 123456
recipient.lastModified = Format.formatDateTimeInter(getCurrentDate());
recipient.save();

For raw SQL with 📕 sqlExec, use the $(curdate) placeholder — no string conversion needed:

sqlExec("UPDATE nmsDelivery SET tsLastModified=$(curdate) WHERE iId=$(l)", 1234);

Not sure what to choose between NLWS and sqlExec? Follow the AC Database article

Validation & troubleshooting

How to test

To test date handling in Adobe Campaign, ensure that the getCurrentDate() function is used for consistency across server timezones. Verify that date conversions between JavaScript, XML, and SQL are working as expected.

Common errors

  • Timezone inconsistencies: Always prefer getCurrentDate() over new Date() to avoid timezone issues.
  • Invalid date formats: Ensure that date strings are in the correct format when converting between runtimes.

Going further

SQL expressions

Create a Year-to-Date (YTD) filter in a queryDef condition:

// Fixed start of year
ToDateTime("2019-01-01");

// Dynamic start of current year
ToDateTime(Year(GetDate()) + "-01-01");

MomentJS external library

For complex date arithmetic, MomentJS can be loaded as an external library. See Use JavaScript libraries in Adobe Campaign.

loadLibrary("vendor:moment");

var today = moment(); // today July 2nd 2020
vars.yesterday = moment().subtract(1, "day").format("YYYY-MM-DD"); // yesterday July 1st 2020
vars.lastDayOfMonth = today
  .clone()
  .endOf("month")
  .format("YYYY-MM-DD HH:mm:ss"); // July 31st 2020
vars.lastDayOf12m = today
  .clone()
  .endOf("month")
  .subtract(1, "year")
  .format("YYYY-MM-DD HH:mm:ss"); // July 31st 2019
vars.lastDayOf24m = today
  .clone()
  .endOf("month")
  .subtract(2, "year")
  .format("YYYY-MM-DD HH:mm:ss"); // July 31st 2018
vars.thisMonday = moment().startOf("week");
vars.lastMonday = moment().subtract(7, "days").startOf("week");

⚠️ MomentJS is in maintenance mode upstream. Prefer native formatDate and parseTimeStamp for new code when the built-in helpers cover your use case.