Date Data Type

Dates are values that represent a specific point in time, precise up to the second. They can also be referred to as datetime values. While dates are shown visually under the system's regional settings, in reality they are stored unformatted.

The Date property is stored in Connect database with zero time zone offset, which makes it possible to convert the time correctly in any location. PlanetPress Workflow, however, shows the date/time as it is stored database (with 0 time zone offset). This is expected behavior for the moment and the zone offset must be calculated manually in PlanetPress Workflow.

Extracting dates

To extract data and have that data interpreted as a Date, set the type of the respective field to Date:

  1. Select the field in the data model.
  2. On the Step properties pane, under Field Definition, specify the Type as Date.
  3. Next, under Data Format, specify the Date/Time Format. This format must match the way the date is formatted in the source data; otherwise the data cannot be interpreted as a Date.

Defining a date/time format

A date format is a mask representing the order and meaning of each digit in the raw data, as well as the date/time separators. The mask uses several predefined markers to parse the contents of the raw data. Here is a list of markers that are available in the DataMapper:

  • yy: Numeric representation of the Year when it is written out with only 2 digits (i.e. 13)
  • yyyy: Numeric representation of the Year when it is written out with 4 digits (i.e. 2013)
  • M: Short version of the month name ( i.e. Jan, Aug). These values are based on the current regional settings.
  • MM: Long version of the month name (i.e. January, August). These values are based on the current regional settings.
  • mm: Numeric representation of the month (i.e. 1, 09, 12)
  • D: Short version of the weekday name ( i.e. Mon, Wed). These values are based on the current regional settings.
  • DD: Long version of the weekday name (i.e. Monday, Wednesday). These values are based on the current regional settings.
  • dd: Numeric representation of the day of the month (i.e. 1, 09, 22)
  • hh: Numeric representation of the hours
  • nn: Numeric representation of the minutes
  • ss: Numeric representation of the seconds
  • ms: Numeric representation of the milliseconds.
  • ap: AM/PM string.
  • In addition, any constant character can be included in the mask, usually to indicate date/time separators (i.e. / - :) . If one of those characters happens to be one of the reserved characters listed above, it must be escaped using the \ symbol.
The markers that can be used when extracting dates are different from those that are used to display dates in a template (see Date and time patterns).

Examples of masks

Value in raw data​ Mask to use
June 25, 2013 MM dd, YYYY
06/25/13 ​mm/dd/yy
​2013.06.25 yyyy.mm.dd
2013-06-25 07:31 PM yyyy-mm-dd hh:nn ap
​2013-06-25 19:31:14.1206 yyyy-mm-dd hh:nn:ss.ms
Tuesday, June 25, 2013 @ 7h31PM ​DD, MM dd, yyyy @ hh\hnnap

Entering a date using JavaScript

In several places in the DataMapper, Date values can be set through a JavaScript. For example:

  • In a field in the Data Model. To do this, go to the Steps pane and select an Extract step. Then, on the Step properties pane, under Field Definition click the Add JavaScript Field button (next to the Field List drop-down). Type the JavaScript in the Expression field. (To rename the field, click the Order and rename fields button.)
  • In a Preprocessor property. To do this, go to the Steps pane and select the Preprocessor step. Then, on the Step properties pane, under Properties add a property, specify its Type as Date and put the JavaScript in the Default Value field.

The use of the JavaScript Date() object is necessary when creating dates through a JavaScript expression. For more information, see w3schools - JavaScript Dates and w3schools - Date Object.

Example

The following script creates a date that is the current date + 30 days:

function addDays(date, days) {
var result = new Date(date);
result.setDate(result.getDate() + days);
return result;
}
addDays(new Date(), 30);
 
  • Last Topic Update: 04/04/2017 10:24
  • Last Published: 7/6/2017 : 9:48 AM