Template:Datetime rules, tokens, and separators

Datetime format rules

Certain rules are applied to determine if a format is valid. The basic rules are:

1. If a format string contains a numeric datetime token (that is, ND, NM, or NS), then the format string must consist of only one token. Numeric datetime tokens are supported only in format strings for the Sir2000 Field Migration Facility.
2. You must specify at least one time, weekday, or date (except for Sir2000 Field Migration Facility alternate or error formats).
3. Except for "weekday," you must not specify redundant information. More specifically this means:
   • Except for I, no token can be specified twice.
   • At most one year format (contains Y) can be specified.
   • At most one month format (contains MON, Mon, or MM) can be specified.
   • At most one day format (DD or Day) can be specified.
   • At most one weekday format (WKD, Wkd, WKDAY, or Wkday) can be specified.
   • If AM is specified, then PM cannot be specified.
   • At most one fractions-of-a-second format (contains X) can be specified.
   • If DDD is specified, then neither a day nor month format can be specified.
4. If ZYY is specified in a format string, no other token that denotes a variable-length value may be used.
5. If a format string contains other tokens that denote variable length values, then an * token may only appear as the last character of the format string.
6. The DAY token may not be immediately followed by another token whose value may be numeric, regardless of whether the following token represents a variable length value. Thus, DAY may not be followed by *, I, YY, YYYY, CYY, MM, HH, MI, SS, X, XX, or XXX; DAY may not be followed by a decimal digit separator; and DAY may not be followed by a quote followed by a decimal digit.
7. For non-Janus add-on products:

   When a pair of format strings are used for transforming date values, for example, for $Sir_DateCnv, #DATECNV, or processing of updates to fields that are connected by a SIRFIELD RELATE command, additional rules apply to the pattern matching tokens:

   • If one of the format strings includes one or more I tokens, then the other format string must contain the same number of I tokens. Note that the placement of I tokens within the format strings is not restricted. The I tokens are processed left to right, with each character from the input string that corresponds to the nth I token in the input format being copied unchanged to the character position in the output string that corresponds to the nth I token in the output format.
   • If one of the format strings contains an * token, then the other format string must also contain an * token. All of the characters from the input string that correspond to the * token in the input format, if any, are copied unaltered to the output string, beginning in the position that corresponds to the * token in the output format.
8. The maximum length of a format string is 100 characters (except 31 for SirDBA).

Datetime format tokens

The valid tokens in a date format are shown in the following list. In general, the output format rule for a token is shown: that is, the result when a value from an SQL client is converted to operate on a DATETIME field, or the result when a DATETIME or SMALLDATETIME numeric value is converted to a datetime character string in a SOUL %variable.

The input format rules are less strict: for example, all of the tokens that convert from an alphabetic string (for example, MON) work with any case string (for example, jan or JAN or Jan).

For some of the functions, the input format rule for a token is the same as the output format rule; this is the definition of "strict date format matching." However, non-strict functions sometimes allow a string to match a token on input that would not be produced by that token on output.

All of the tokens that match alphabetic strings (for example, MON) match any case for non-strict matching. For other tokens that have differing strict and non-strict matching rules, usage notes for them are contained below in Datetime and format examples. Each input datetime format argument in the description of a function specifies whether the use of the format observes strict or non-strict format matching.

NM	Numeric datetime value containing the number of milliseconds (1/1000 of a second) since January 1, 1900 at 12:00 AM. (This token is allowed only in the Sir2000 Field Migration Facility.)
NS	Numeric datetime value containing the number seconds since January 1, 1900 at 12:00 AM. (This token is allowed only in the Sir2000 Field Migration Facility.)
ND	Numeric date value containing the number of days since January 1, 1900. (This token is allowed only in the Sir2000 Field Migration Facility.)
*	Ignore entire variable-length substring matching pattern, if any, only when retrieving a date value. Substitute with null string when only creating a date value. When copying date values, copy entire variable-length substring matching pattern, if any, from the input value to the location identified by * token in output string.
I	For Janus products and SirDBA: ignore corresponding input character. For other products: ignore corresponding input character when only retrieving a date value. Store a blank in corresponding output character when only creating a date value. When copying date values, copy each character matching an I token from from the input value to the location in the output string identified by the corresponding I token in the output format.
"	Following character is "quoted," that is, it acts as a separator character.
YYYY	4-digit year
YY	2-digit year
CYY	Year minus 1900 (3 digits, including any leading zero).
ZYY	Year minus 1900, two-digit or three-digit year number, excluding any leading zero (variable length data). Non-strict functions allow a three-digit number with leading zero on input, but any number less than 100 always produces a two-digit number on output. See Datetime and format examples.
MONTH	Full month name (uppercase variable length). For Janus products: if used as an argument to a method or $function for converting from a string, this is the same as Month. Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.
Month	Full month name (mixed-case variable length). For Janus products: if used as an argument to a function for converting from a string, this is the same as MONTH. Non-strict functions allow any mixture of uppercase and lowercase on input, but initial uppercase letter followed by all lowercase is always produced on output.
MON	Three-character month abbreviation (uppercase). For Janus products: if used as an argument to a function for converting from a string, this is the same as Mon. Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.
Mon	Three-character month abbreviation (mixed case). For Janus products: if used as an argument to a function for converting from a string, this is the same as MON. Non-strict functions allow any mixture of uppercase and lowercase on input, but initial uppercase letter followed by all-lowercase is always produced on output.
MM	Two-digit month number. For Janus products: if used as an argument to a function for converting from a string, this is the same as BM (leading blank is allowed). Non-strict functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. Note: A common mistake is to use MM instead of MI for minutes.
BM	Two-character month number. For Janus products: if used as an argument to a function for converting from a string, this is the same as MM. If less than 10, first character is blank. Non-strict functions allow a two-digit number with leading zero on input, but any number less than 10 always produces a blank followed by a decimal digit on output.
DDD	Three-digit Julian day number.
DD	Two-digit day number. For Janus products: if used as an argument to a function for converting from a string, this is the same as BD (leading blank is allowed). Non-strict functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output.
BD	Two-character day number. If less than 10, first character is blank. For Janus products: if used as an argument to a function for converting from a string, this is the same as DD. Non-strict functions allow a two-digit number with leading zero on input, but any number less than 10 always produces a blank followed by a decimal digit on output.
DAY	One-digit or two-digit day number (variable length data). Non-strict functions allow a two-digit number with leading zero on input, but any number less than 10 always produces a one-digit number on output.
WKDAY	Full day of week name (upper case variable length). For Janus products: if used as an argument to a function for converting from a string, this is the same as Wkday. Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.
Wkday	Full day of week name (mixed-case variable length). For Janus products: if used as an argument to a function for converting from a string, this is the same as WKDAY. Non-strict functions allow any mixture of uppercase and lowercase on input, but initial uppercase letter followed by all-lowercase is always produced on output.
WKD	Three-character day of week abbreviation (uppercase). For Janus products: if used as an argument to a function for converting from a string, this is the same as Wkd. Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.
Wkd	Three-character day of week abbreviation (mixed case). For Janus products: if used as an argument to a function for converting from a string, this is the same as WKD. Non-strict functions allow any mixture of uppercase and lowercase on input, but initial uppercase letter followed by all-lowercase is always produced on output.
HH	Two-digit hour number. Non-strict functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. For Janus products: if used as an argument to a function for converting from a string, this is the same as BH (leading blank is allowed).
BH	Two-digit hour number. If less than 10, first character is blank. For Janus products: if used as an argument to a function for converting from a string, this is the same as HH. Non-strict functions allow a two-digit number with leading zero on input, but any number less than 10 always produces a blank followed by a decimal digit on output.
MI	Two-digit minute number. Note: A common mistake is to use MM instead of MI for minutes.
SS	Two-digit second number.
X	Tenths of a second.
XX	Hundredths of a second.
XXX	Thousandths of a second (milliseconds).
AM	AM/PM indicator.
PM	AM/PM indicator.

Datetime format separators

The valid separators in a date format are:

• blank
• apostrophe (')
• slash (/)
• colon (:)
• hyphen (-)
• back slash (\)
• period (.)
• comma (,)
• underscore (_)
• left parenthesis ( ( )
• right parenthesis ( ) )
• plus (+)
• vertical bar (|)
• equal (=)
• ampersand (&)
• at sign (@)
• sharp (#)
• the decimal digits (0 - 9).

In addition, any character may be a separator character if preceded by the quoting character (").

See Datetime and format examples for examples that include the use of various separator characters.