date

The date function converts a string type into a datetime type using a format that you specify. This function allows you to include static characters such as a slash (/), which gives you access to full date support.

Common use

The date function is commonly used in SQL maps to convert a date that is stored in the database as a string type into a datetime variable or field. It is also used in print forms. Because the document name or ref data can only be updated with a standard rule against a string field, a date field has to be defined as string then converted if necessary.

Syntax

Datetime = date("format",string);

where:

  • datetime = datetime variable (month specified as 1-12)
  • format = desired date format
  • string = string variable

Example

datetime d;
d = date(2012,4,6);
d = date(2012,4,6,12,0);
d = date("%y/%m/%d","12/4/6");
d = date("%y/%m/%d",#strdate);

Format specifiers

Table 1. Format specifiers
Format Specifier Description
%8 ISO-8601 date format
YYYYMMDDTHHMMSS.mmmZ

Four-digit year, two-digit month, two-digit day, T (time) indicator, two-digit hour, two-digit minutes, two-digit seconds in Universal Time (also called Zulu Time or Greenwich Mean Time), Z (Zulu time) indicator (example: 20031209T123000.000Z)

Note: This date format cannot be combined with any other format specifier.
%a Abbreviated weekday name
%A Full weekday name
%b Abbreviated month name
%B Full month name
%d Day of the month as a decimal number (01 – 31)
%D ISO-8601 date format (date component only)
YYYYMMDDZ

This date format cannot be combined with any other format specifier.
%H Hour in 24-hour format (00 – 23)
%I Hour in 12-hour format (01– 12)
%j Day of the year as a decimal number (001 – 366)
%m Month as a decimal number (01 – 12)
%M Minute as a decimal number (00 – 59)
%S Second as a decimal number (00 – 59)
%U Week of the year as a decimal number, with Sunday as the first day of the week (00 – 51)
%w Weekday as a decimal number (0 – 6, with Sunday as "0")
%W Week of the year as a decimal number, with Monday as the first day of the week (00 – 51)
%y Year without the century as a decimal number (00 – 99)
%Y Year with the century as a decimal number
%% Percent sign