JAL: /* Datetime format rules */ add link

2018-02-01T22:27:16Z

Datetime format rules: add link

← Older revision		Revision as of 22:27, 1 February 2018
Line 64:		Line 64:
	</ul></li>		</ul></li>

	<li>The maximum length of a format string is 100 characters (except 31 for <var class="product">SirDBA</var>).		<li>The maximum length of a format string is 100 characters (except 31 for <var class="product">[[SirDBA]]</var>).
	</ol>		</ol>

JAL: add link

2018-02-01T22:23:26Z

add link

← Older revision		Revision as of 22:23, 1 February 2018
Line 6:		Line 6:
	<var>ND</var>, <var>NM</var>, or <var>NS</var>), then the		<var>ND</var>, <var>NM</var>, or <var>NS</var>), then the
	format string must consist of only one token.		format string must consist of only one token.
	Numeric datetime tokens are supported only in format strings for the Sir2000 Field Migration Facility.		Numeric datetime tokens are supported only in format strings for the [[Media:SirfieldNew.pdf\|Sir2000 Field Migration Facility]].

	<li>You must specify at least one time, weekday, or date (except for Sir2000 Field Migration Facility alternate or error formats).		<li>You must specify at least one time, weekday, or date (except for Sir2000 Field Migration Facility alternate or error formats).

JAL: Created page with " ===Datetime format rules=== Certain rules are applied to determine if a format is valid. The ba..."

2018-02-01T00:25:46Z

Created page with " ===Datetime format rules=== Certain rules are applied to determine if a format is valid. The ba..."

New page

===Datetime format rules===
Certain rules are applied to determine if a format is valid. The basic rules are:
<ol>
<li>If a format string contains a numeric datetime token (that is,
<var>ND</var>, <var>NM</var>, or <var>NS</var>), 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.

<li>You must specify at least one time, weekday, or date (except for Sir2000 Field Migration Facility alternate or error formats).

<li>Except for "weekday," you must not specify redundant information.
More specifically this means:
<ul>
<li>Except for <var>I</var>, no token can be specified twice. </li>

<li>At most one year format (contains <var>Y</var>) can be specified.</li>

<li>At most one month format (contains <var>MON</var>, <var>Mon</var>, or <var>MM</var>) can be specified. </li>

<li>At most one day format (<var>DD</var> or <var>Day</var>) can be specified. </li>

<li>At most one weekday format (<var>WKD</var>, <var>Wkd</var>, <var>WKDAY</var>, or <var>Wkday</var>) can be specified. </li>

<li>If <var>AM</var> is specified, then <var>PM</var> cannot be specified. </li>

<li>At most one fractions-of-a-second format (contains <var>X</var>) can be specified. </li>

<li>If <var>DDD</var> is specified, then neither a day nor month format can be specified. </li>
</ul>

<li>If <var>ZYY</var> is specified in a format string, no other token that denotes a variable-length value may be used.

<li>If a format string contains other tokens that denote variable
length values, then an <var>*</var> token may only appear as the last character of the format string.

<li>The <var>DAY</var> 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, <var>DAY</var> may not be followed by <var>*</var>, <var>I</var>, <var>YY</var>, <var>YYYY</var>, <var>CYY</var>, <var>MM</var>, <var>HH</var>, <var>MI</var>, <var>SS</var>,
<var>X</var>, <var>XX</var>, or <var>XXX</var>; <var>DAY</var> may not be followed by a decimal digit separator; and <var>DAY</var> may not be followed by a quote followed by a decimal digit. </li>


<li>For non-Janus add-on products:

When a pair of format strings are used for transforming date values,
for example, for <var>[[$Sir_DateCnv]]</var>, <var>[[Fast/Unload standard functions#.23DATECNV: Convert datetime to different format|#DATECNV]]</var>, or processing of updates to fields that are connected by a <var>SIRFIELD RELATE</var> command, additional rules apply to the pattern matching tokens: 
<ul>
<li>If one of the format strings includes one or more <var>I</var>
tokens, then the other format string must contain the same
number of <var>I</var> tokens.
Note that the placement of <var>I</var> tokens within the format
strings is not restricted.
The <var>I</var> tokens are processed left to right, with
each character from the input string that corresponds to the
nth <var>I</var> token in the input format being copied unchanged to the character position in the output string that corresponds to the
nth <var>I</var> token in the output format. </li>

<li>If one of the format strings contains an <var>*</var> token,
then the other format string must also contain an <var>*</var> token.
All of the characters from the input string that correspond to the
<var>*</var> token in the input format, if any, are copied unaltered
to the output string, beginning in the position that corresponds to
the <var>*</var> token in the output format. </li>
</ul></li>

<li>The maximum length of a format string is 100 characters (except 31 for <var class="product">SirDBA</var>).
</ol>

===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 <var class="product">SOUL</var> %variable.

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

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 and non-strict format matching|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, <var>MON</var>)
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|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.

<table class="thJustBold">
<tr><th>NM</th>
<td>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 <var class="product">Sir2000 Field Migration Facility</var>.)</td></tr>

<tr><th>NS</th>
<td>Numeric datetime value containing the number seconds since January 1, 1900 at 12:00 AM. (This token is allowed only in the <var class="product">Sir2000 Field Migration Facility</var>.)</td></tr>

<tr><th>ND</th>
<td>Numeric date value containing the number of days since January 1, 1900. (This token is allowed only in the <var class="product">Sir2000 Field Migration Facility</var>.)</td></tr>

<tr><th>*</th>
<td>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 <code>*</code> token in output string.</td></tr>

<tr><th>I</th>
<td>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 <var>I</var> token in the output format.</td></tr>

<tr><th>"</th>
<td>Following character is "quoted," that is, it acts as a separator character.</td></tr>

<tr><th>YYYY</th>
<td>4-digit year</td></tr>

<tr><th>YY</th>
<td>2-digit year</td></tr>

<tr><th>CYY</th>
<td>Year minus 1900 (3 digits, including any leading zero).</td></tr>

<tr><th>ZYY</th>
<td>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|Datetime and format examples]]. </td></tr>

<tr><th>MONTH</th>
<td>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 <var>Month</var>.
Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.</td></tr>

<tr><th>Month</th>
<td>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 <var>MONTH</var>.
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.</td></tr>

<tr><th>MON</th>
<td>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 <var>Mon</var>.
Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.</td></tr>

<tr><th>Mon</th>
<td>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 <var>MON</var>.
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.</td></tr>

<tr><th>MM</th>
<td>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 <var>BM</var> (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 <var>MM</var> instead of <var>MI</var> for minutes. </td></tr>

<tr><th>BM</th>
<td>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 <var>MM</var>. 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.</td></tr>

<tr><th>DDD</th>
<td>Three-digit Julian day number.</td></tr>

<tr><th>DD<td>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 <var>BD</var> (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.</td></tr>

<tr><th>BD</th>
<td>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 <var>DD</var>.

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. </td></tr>

<tr><th>DAY</th>
<td>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.</td></tr>

<tr><th>WKDAY</th>
<td>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 <var>Wkday</var>.
Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.</td></tr>

<tr><th>Wkday</th>
<td>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 <var>WKDAY</var>.
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.</td></tr>

<tr><th>WKD</th>
<td>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 <var>Wkd</var>.
Non-strict functions allow any mixture of uppercase and lowercase on input, but all-uppercase is always produced on output.</td></tr>

<tr><th>Wkd</th>
<td>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 <var>WKD</var>.
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.</td></tr>

<tr><th>HH</th>
<td>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 <var>BH</var> (leading blank is allowed).</td></tr>

<tr><th>BH</th>
<td>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 <var>HH</var>.
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.</td></tr>

<tr><th>MI</th>
<td>Two-digit minute number.
Note: A common mistake is to use <var>MM</var> instead of <var>MI</var> for minutes. </td></tr>

<tr><th>SS</th>
<td>Two-digit second number.</td></tr>

<tr><th>X</th>
<td>Tenths of a second.</td></tr>

<tr><th>XX</th>
<td>Hundredths of a second.</td></tr>

<tr><th>XXX</th>
<td>Thousandths of a second (milliseconds).</td></tr>

<tr><th>AM</th>
<td>AM/PM indicator.</td></tr>

<tr><th>PM</th>
<td>AM/PM indicator.</td></tr>
</table>

===Datetime format separators===
The valid separators in a date format are:
<ul>
<li>blank
<li>apostrophe (')
<li>slash (/)
<li>colon (:)
<li>hyphen (-)
<li>back slash (\)
<li>period (.)
<li>comma (,)
<li>underscore (_)
<li>left parenthesis ( ( )
<li>right parenthesis ( ) )
<li>plus (+)
<li>vertical bar (|)
<li>equal (=)
<li>ampersand (&)
<li>at sign (@)
<li>sharp (#)
<li>the decimal digits (0 - 9).
</ul>
In addition, any character may be a separator character if preceded by
the quoting character (").

See [[#Datetime and format examples|Datetime and format examples]] for examples that include the use of various separator characters.

Template:Datetime rules, tokens, and separators - Revision history

JAL: /* Datetime format rules */ add link

JAL: add link

JAL: Created page with " ===Datetime format rules=== Certain rules are applied to determine if a format is valid. The ba..."