Calculation Formulas

Back to Calculation Formulas Overview
Back to Functions Overview

Date/Time Patterns

This sub-section describes the format of the date/time patterns that must be specified for the functions "ToDateText" and "ToMillis" to convert a numerical date/time value (represented in milliseconds since "Jan. 1st 1970, 00:00:00 GMT") into a formatted output string, or vice versa.

The pattern format described in this section applies both to the formatting process, when a numerical date/time value is converted into a formatted text string, as well as to the parsing process, when a text string is parsed to convert it back into the numerical date/time value.

Note: This is a direct representation of the formatting offered by the Java class SimpleDateFormat, so the description of the format as given here has been taken more or less directly from the documentation of that Java class.

Important Note: For date/time formatting and parsing, by default, the U.S. locale is used. This means that if locale specific values (like names of months, weekdays, era-designators, and so on) are required, they will, by default, be given (or expected) as the U.S. locale values. If you do not want to use the default U.S. locale, you need to specify a locale in the relevant function.
Similarly with timezones: The formatting function "ToDateText" requires you to supply a timezone as an argument. But the parsing function "ToMillis" will, by default, interpret the given date/time text as relative to the time zone of the server, unless the text also contains timezone information (and the format pattern specifies this).

Date and Time Patterns

Date and time formats are specified by pattern strings. Within pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string (see below for details). Text can be quoted using single quotes (') to avoid interpretation. In a quoted text, the "double" single quote ('') represents a single quote. All other characters are not interpreted; they are simply copied into the output string during formatting or matched against the input string during parsing.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved).

Note: the letters are case-sensitive, so the same letter has different meanings in its uppercase or lowercase version. Each pattern letter has a specific "presentation" in the created final string (during formatting) or in the parsed string (during parsing). For example, presentation types may be "Text", "Number", "Year" and so on. More details about the presentations and their meanings follow below.

Commonly Used Pattern Letters:

Special Pattern Letters:

Note: The value of "calendar week in year" and "calendar week in month" depends on the locale where it is used, on the conventions in the given locale about which weekday is interpreted as the first day of the week (usually either "Monday" or "Sunday"), and under what circumstances a week that falls partially into one year (or month) and partially into the next is interpreted as belonging to the one year (or month) or the other.

Note: The "weekday ordinal in month" indicates the ordinal number of the weekday of the given date/time in the given month. So, for the first Monday in a month, the ordinal is "1", as it is for the first Tuesday, Wednesday and so on. For the second Monday in a month the ordinal is "2", etc.

Presentation Description

Pattern letters are usually repeated, as their number determines the exact presentation:

• Text: For formatting, if the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used, if available.
  For parsing, both forms are accepted, independent of the number of pattern letters.
   
• Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.
  For parsing, the number of pattern letters is ignored unless it is needed to separate two adjacent fields.
   
• Year: For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a "Number" (see above).
  For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern "MM/dd/yyyy", the text "01/11/12" parses to Jan. 11, 12 AD.
  For parsing with the abbreviated year pattern ("y" or "yy"), LISTSERV Maestro must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the current time. For example, using the pattern "MM/dd/yy" on Jan. 1, 1997, the text "01/11/12" would be interpreted as Jan. 11, 2012, while the text "05/04/64" would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that is not all digits (for example "-1"), is interpreted literally. So, "01/02/3" or "01/02/003" are parsed, using the same pattern, as Jan. 2, 3 AD. Likewise, "01/02/-3" is parsed as Jan. 2, 4 BC.
   
• Month: If the number of pattern letters is 1 or 2, the month is interpreted as "Number", if it is 3 or more, it is interpreted as "Text". Therefore, if the month is interpreted either as "Number" or "Text", the applicable "Number"/ "Text" interpretation rules apply (see above). For example: 1 letter will be a "Number" that is not padded, 2 letters will be a "Number" that is padded, 3 letters will be a "Text" using the abbreviated form and 4 or more letters will be a "Text" using the long form.
   
• General time zone: For formatting, the time zone is handled as "Text" if it has a name. If not, it is given as a GMT offset value in the format "GMT[+|-]HH:MM", where "HH" is the hours between 0 and 23 (one or two digits may be zero-padded to the left) and "MM" is the minutes between 00 and 59 (always two digits, zero-padded to the left if necessary). For example: "GMT+8:00", "GMT+08:00", "GMT-12:45".
  For parsing, see "Time zone parsing" below.
   
• RFC 822 time zone: For formatting, the RFC 882 4-digit time zone format is used: "[+|-]HHMM", where "HH" is the hours as two digits, between 00 and 23 (zero-padded to the left if necessary) and "MM" is the minutes as two digits, between 00 and 59 (zero-padded to the left if necessary).
  For parsing, see "Time zone parsing" below.
   
• Time zone parsing: For parsing of a time zone, it does not matter if the format pattern specifies a "General time zone" or a "RFC 822 time zone". In both cases, all three types of time zone specifications are accepted:
   
  • Time zone given as "Text" (if given with an ID recognized by Maestro).
     
  • Time zone given as GMT offset (see "General time zone").
     
  • Time zone given as RFC 882 4-digit notation (see RFC 822 time zone).

Examples

The following examples show how date and time patterns are interpreted in the default U.S. locale with the "U.S. Pacific Time" time zone. The given date and time are "2001-07-04 12:08:56" local time in that time zone.

Predefined Locales

When specifying a locale to be used for date/time formatting in "ToDateText" or for parsing in "ToMillis", you can either specify the specific ISO codes of the desired country and language (see the description of "ToDateText" and "ToMillis), or you can simply specify one of the following predefined locale names, which stands as a shortcut for the given language and country combination: