Datetime string formats: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (typo)
m (add category)
 
(31 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Datetime formats==
The discussion of datetime processing on this page applies to $functions, #functions, and commands added to the Rocket M204 products in version 7.5 of Model 204. These added functions and commands share "datetime formats" that differ from those in the pre-7.5 standard Model 204 related date $functions.
The string representation of a date is described by a '''datetime format'''.
This value is a character string, composed of the concatenation of
tokens (for example, <code>YYYY</code> for a four-digit year, and <code>MI</code> for minutes) and separator characters
(for example, <code>/</code> in <code>MM/DD/YY</code> for two-digit month, day, and year separated by slashes).
These datetime format strings are used in many methods, commands, and
$functions. Since all methods that operate on strings are functions, methods and $functions are collectively referred to as ''functions'' on this page.
The rules for these datetime format strings are consistent
throughout all Sirius functions, though certain uses of these strings
might impose extra restrictions.
For example, a leading blank may match an <var>HH</var>, <var>DD</var>, or <var>MM</var> token in
[[Object oriented programming in SOUL|SOUL methods]],
but it may not in some cases in other Rocket products.
There are certain rules applied to determine if a format is valid. The basic rules are:
<ul>
<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 only supported in format strings for the FMF2K.. -->
 
<li>You must specify at least one time, weekday, or date. <!-- except for &FMF2K alternate or error formats. -->


<li>Except for "weekday," you must not specify redundant information.
Although the discussion here tends to emphasize the SOUL [[SOUL_$functions#datetime|$Sir_Date<i>xxx</i> $functions]], these datetime format strings are used in several Rocket Model&nbsp;204 add-on products, including:
More specifically this means:
<ul>
<ul>
<li>Except for <var>I</var>, no token can be specified twice. </li>
<li><var class="product">[[Fast/Unload datetime processing considerations|Fast/Unload]]</var> </li>


<li>At most one year format (contains <var>Y</var>) can be specified.</li>
<li><var class="product">[[Media:JoclrNew.pdf|Janus Open Client]]</var> </li>


<li>At most one month format (contains <var>MON</var>, <var>Mon</var>, or <var>MM</var>) can be specified. </li>
<li><var class="product">[[Media:JosrvrNew.pdf|Janus Open Server]]</var>
</li>


<li>At most one day format (<var>DD</var> or <var>Day</var>) can be specified. </li>
<li><var class="product">[[Media:JsdsrNew.pdf|Janus Specialty Data Store]]</var> </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><var class="product">[[Janus Web Server]]</var> </li>


<li>If <var>AM</var> is specified, then <var>PM</var> can not be specified. </li>
<li><var class="product">[[SirDBA]]</var> </li>


<li>At most one fractions-of-a-second format (contains <var>X</var>) can be specified. </li>
<li><var class="product">[[Media:SirfieldNew.pdf|Sir2000 Field Migration Facility]]</var>


<li>If <var>DDD</var> is specified, then neither a day nor month format can be specified. </li>
<li><var class="product">[[Sir2000 User Language Tools]]</var> </li>
</ul>
</ul>
<br>


<li>If <var>ZYY</var> is specified in a format string, no other token that denotes a variable-length value may be used.
==Datetime formats==
 
The string representation of a date is described by a '''datetime format'''.
<li>If a format string contains other tokens that denote variable
This value is a character string, composed of the concatenation of these:
length values, then an <var>*</var> token may only appear as the last character
<ul>
of the format string.
<li>Tokens
<p>
For example, <var>YYYY</var> for a four-digit year, and <var>MI</var> for
minutes.</p></li>


<li>The <var>DAY</var> token may not be immediately followed by another token whose value may be numeric, regardless of whether the following
<li>Separator characters
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>
 
<!-- following list item was initially commented out, a Sirius conditional include -->
<li><i>For non-Janus products and for the SirDBA product only:</i>
<p>
<p>
When a pair of format strings are used for transforming date values,
For example, forward slash (<tt>/</tt>) in <var>MM/DD/YY</var> for two-digit month, day, and year separated by slashes.</p></li>
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 SIRFIELD RELATEd fields,
additional rules apply to the pattern matching tokens: </p>
<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
<i>n</i>th <var>I</var> token in the input format being copied unchanged to the
character position in the output string that corresponds to the
<i>n</i>th <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>
The $functions or #functions with both an input and output format,
for example, <var>$Sir_DateCnv</var> or <var>#DATECNV</var>, are only available in the <var class="product">Sir2000 User Language Tools Functions</var> product.
</li>
 
<li>The maximum length of a format string is 100 characters (except 31 for <var class="product">SirDBA</var>).
</ul>
</ul>
   
   
<p class="note"><b>Note:</b> A common mistake is to use <var>MM</var> for minutes; it should be <var>MI</var>. </p>
These datetime format strings are used in many methods, commands, and
 
$functions. Since all methods that operate on strings are functions, methods and $functions are collectively referred to as ''functions'' on this page.
==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 date format matching."
The rules for these datetime format strings are consistent
<!--
wherever these formats are used, though certain uses of these strings might impose extra restrictions.
This ensures that values accepted as the update of
For example, a leading blank may match an <var>HH</var>, <var>DD</var>, or <var>MM</var> token in [[Object oriented programming in SOUL|SOUL methods]],
a field will be preserved across a reorg, for related fields.
but it may not in some cases in other Rocket M204 products.
-->
{{Template:Datetime rules, tokens, and separators}}
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 &FMF2K..)</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 &FMF2K..)</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 &FMF2K..)</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.
<p>
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.</p></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.</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. For Janus products: if used as an argument to a function for converting ''from'' a string, this is the same as <var>DD</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.
 
<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. For Janus products: if used as an argument to a function for converting ''from'' a string, this is the same as <var>HH</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>MI</th>
<td>Two-digit minute number.</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>
The valid separators in a date format are:
<ul>
<li>blank
<li>apostrophe (<b>'</b>)
<li>slash (<b>/</b>)
<li>colon (<b>:</b>)
<li>hyphen (<b>-</b>)
<li>back slash (<b>\</b>)
<li>period (<b>.</b>)
<li>comma (<b>,</b>)
<li>underscore (<b>_</b>)
<li>left parenthesis (<b> ( </b>)
<li>right parenthesis (<b> ) </b>)
<li>plus (<b>+</b>)
<li>vertical bar (<b>|</b>)
<li>equal (<b>=</b>)
<li>ampersand (<b>&</b>)
<li>at sign (<b>@</b>)
<li>sharp (<b>#</b>)
<li>the decimal digits (<b>0</b> - <b>9</b>).
</ul>
In addition, any character may be a separator character if preceded by
the quoting character (<b>"</b>).


==Valid datetimes==
==Valid datetimes==
Line 281: Line 71:


==Processing dates with two-digit year values==
==Processing dates with two-digit year values==
A date field with only two digits for the year value is capable of representing a range of up to one hundred years.
{{Template:Processing dates with two-digit year values}}
When we compare a pair of two-digit year values, we are accustomed to
thinking of the century as fixed, so that all dates are either <code>19xx</code> or <code>20xx</code>.
However, a date field with two-digit year values can actually represent
dates from two different centuries, provided that the ''range'' of dates does not exceed 100 years.
 
===Using CENTSPAN===
<var>CENTSPAN</var> provides a mechanism for unambiguously converting dates with
two-digit year values into dates with four-digit year values.
The <var>CENTSPAN</var> mechanism allows two-digit year values to span two centuries without confusion.
<var>CENTSPAN</var> identifies the four-digit year value that is the ''start''
of a range of years represented by the two-digit year values.
<var>CENTSPAN</var> may be specified as an ''absolute'' unsigned four-digit
value between 1753 and 9999, or it may be specified as a ''relative''
signed value between -99 and +99, inclusive.
A relative <var>CENTSPAN</var> value is dynamically converted to an ''effective''
absolute value before it is used to perform a <var>YY</var> to <var>YYYY</var> conversion.
The effective <var>CENTSPAN</var> value is formed by adding the relative <var>CENTSPAN</var> to the
current four-digit year value at the time the relative value is converted.
<p class="figure">
[[File:Centspan.jpg|border|450px]]
</p>
 
A simple algorithm is used to convert a two-digit year value (<var>YY</var>) to a
four-digit year value, using a four-digit absolute or effective
<var>CENTSPAN</var> value (HHLL).
If the two-digit year value is less than the low-order two digits of the
<var>CENTSPAN</var> value, then the resulting century is one greater than the
high-order two digits of the <var>CENTSPAN</var> value.
Otherwise the resulting century is the same as the high-order two digits of the <var>CENTSPAN</var> value.
Using all one hundred available years for mapping two-digit year
values can cause significant confusion and result in data integrity errors.
This is because dates just above and just below the 100-year window are mapped to the other end of the window.
From our previous example, the date "47" will be interpreted
as 1947, when it could have conceivably been 2047.
Simlarly, the date "46" will be interpreted as 2046, when it might have been 1946.
<p class="figure">
[[File:Spam1.jpg|border|300px]]
</p>
 
If <var>CENTSPAN</var> is set to a value that is too high, dates that are just prior to <var>CENTSPAN</var> will appear to occur 100 years hence.
If <var>CENTSPAN</var> is set to a value that is too low, dates that fall just
after <code>CENTSPAN+99</code> will appear to have occurred 100 years earlier.
A full one-hundred year window also can not detect attempts to represent
more than one hundred years of values with a two-digit year.
 
===Using SPANSIZE===
To protect from the ambiguities that
can occur at each end of the 100-year window defined by <var>CENTSPAN</var>,
<var>SPANSIZE</var> is used to restrict the size of the window used for mapping two-digit year values.
The effect is to create two ''guard bands''', one just below the
date window and one just above.
An attempt to represent a date value that lands in a guard band produces an error.
Each guard band contains <code>CENTSPAN - SPANSIZE</code> years, hence a <var>SPANSIZE</var> of 100 removes the protection.
The default <var>SPANSIZE</var> is a value that you can customize in your load module.
If you do not customize it, the value of <var>SPANSIZE</var> is 90, which provides protection
for two ten-year windows: one below the <var>CENTSPAN</var> setting and one starting at <code>CENTSPAN+90</code>.
From our previous example:
<p class="figure">
[[File:Spam2.jpg|border|300px]]
</p>
 
An attempt to represent the values "37" through "46" is rejected.
This protects the range 1937 through 1946 as well as the range 2037 through 2046.
Note that an intended value of 2047, expressed as "47" will be accepted and interpreted as 1947.
In general a smaller <var>SPANSIZE</var> provides the highest assurance of correct mappings.
However, any setting of <var>SPANSIZE</var> less than 100 will probably detect the
case where a range greater than one hundred years is being used.


==Strict and non-strict format matching==
==Strict and non-strict format matching==
As mentioned above, for some of the functions, the input format rule for a token is the same as the output format rule;
As mentioned above in [[#Datetime formats|Datetime formats]], 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."
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.
However, non-strict functions sometimes allow a string to match a token on input that would not be produced by that token on output.


The types of strict matching are as follows:
The types of strict matching are as follows:
<table class="thJustBold">
{{Template:Datetime format matching}}
<tr><th nowrap>Alpha tokens</th>
<p>
<td>For alphabetic tokens (for example, <code>Month</code>), a strict match requires the input value to be the correct case. For example, the "MON" token is strictly matched by "JAN" but not by "Jan", and the reverse is true for the "Mon" token. For non-strict matching, the alpabetic tokens are matched by any combination of uppercase and lowercase input.</td></tr>
The strict functions are only available in the <var class="product">[[Sir2000 User Language Tools]]</var>. With non-strict date $functions, however, you can use the following technique to check a datetime string using strict rules:  
 
<tr><th>HH, MM, DD</th>
<td>For these tokens, a strict match requires a leading zero for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading blank followed by a single numeric digit.</td></tr>
 
<tr><th>BH, BM, BD</th>
<td>For these tokens, a strict match requires a leading blank for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading zero followed by a numeric digit.</td></tr>
 
<tr><th>DAY</th>
<td>For this token, a strict match requires a single digit for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading zero followed by a numeric digit.</td></tr>
 
<tr><th>ZYY</th>
<td>For this token, a strict match requires two digits for values less than 100. For non-strict matching, a value less than 100 can also be represented by a leading zero followed by a two numeric digits.</td></tr>
</table>
 
Since the strict functions are only available in the <var class="product">[[Media:Ul2krNew.pdf|Sir2000 User Language Tools]]</var>, if you want to check a datetime string using strict rules, you can use the following technique with the non-strict date functions:
<p class="code">if <date> eq &apos;' or
  <date> ne <date>:stringToMilliseconds(<format>):millisecondsToString(<format>) then
  <error handling>
end if
</p>
</p>
<p class="code">If <i>date</i> Eq &apos;' Or <i>date</i> NE $Sir_NM2Date(-
  $Sir_Date2NM(<i>date</i>, <i>fmt</i>), <i>fmt</i>) Then
  <i>error handling</i>
End If </p>
<p>
Or, using SOUL OO functions: </p>
<p class="code">if <i>date</i> eq &apos;' or
  <i>date</i> ne <i>date</i>:[[StringToMilliseconds (String function)|stringToMilliseconds]](<i>format</i>):[[MillisecondsToString (Float function)|millisecondsToString]](<i>format</i>) then
  <i>error handling</i>
end if </p>


==Datetime and format examples==
==Datetime and format examples==
The extensive set of format tokens is shown in [[Datetime string formats#Datetime formats|Datetime formats]].
The extensive set of format tokens is shown above in [[Datetime string formats#Datetime format tokens|Datetime format tokens]].
These tokens and the various separator characters can be combined in almost limitless possibility, giving
These tokens and the various separator characters can be combined in almost limitless possibility, giving
rise to an extremely large set of datetime formats.
rise to an extremely large set of datetime formats.
Line 390: Line 100:
This section provides examples of some common datetime formats, and
This section provides examples of some common datetime formats, and
also tries to explain the use of some of the format tokens that might not be obvious.
also tries to explain the use of some of the format tokens that might not be obvious.
It also has examples for formats whose usage with the <var class="product">Sirius Functions</var> differs from that
It also has examples for formats whose usage differs depending on whether [[Datetime string formats#Strict and non-strict format matching|strict or non-strict format matching]] is involved.
with other Sirius products.
   
   
Each example format is explained and also presented with some matching datetimes; again, bear in mind that these tokens can be combined in very many ways and only a very few are shown here.
Each example format is explained and also presented with some matching datetimes; again, bear in mind that these tokens can be combined in very many ways and only a very few are shown here.
Line 397: Line 106:
years 1998-2040, as the basis for relative <var>CENTSPAN</var> calculations.
years 1998-2040, as the basis for relative <var>CENTSPAN</var> calculations.


<p class="note"><b>Note:</b> For comparable Fast/Unload examples, see [[Fast/Unload datetime processing considerations#Datetime and format examples|Fast/Unload datetime and format examples]]. </p>
<p class="note"><b>Note:</b> For comparable Fast/Unload examples, see [[Fast/Unload datetime processing considerations#Datetime and format examples|Fast/Unload datetime and format examples]].  
 
<table class="thJustBold">
<tr><th>YYMMDD</th>
<td>This is the common six-digit date format which supports sort order
if all dates are within a single century.
The following SOUL code fragment prints the value <code>OK</code>.
<p class="code">If $sir_date2nd('960229', 'YYMMDD') > -9E12 Then
  Print 'OK'
End If
</p>
<tr><th>YYYYMMDD</th>
<td>This is the common eight-digit date format that supports sort order
with dates in two centuries.
The following SOUL code fragment prints the value <code>19921212</code>.
<p class="code">%n = $sir_date2nd('921212', 'YYMMDD')
Print $sir_nd2date(%n, 'YYYYMMDD')
</p>
<tr><th>MM/DD/YY</th>
<td>This is the U.S. six-digit date format for display.
The following SOUL code fragment prints the value <code>OK</code>.
<p class="code">If $sir_date2nd('12/14/94', 'MM/DD/YY') > -9E12 Then
  Print 'OK'
End If
</p>
<blockquote class="note"><b>Note: </b>
With non-strict format matching, such as <var>$Sir_Date2nd</var>,
the leading zero corresponding to
an <var class="term">MM</var> token may be given as a blank, thus allowing " 7/15/98".
With strict matching, however, such a leading blank is not allowed for <var class="term">MM</var>;
a leading blank month value with a strict $function
(that is, one of the <var class="product">Sir2000 User Language Tools Functions</var>) requires the <var class="term">BM</var> token.
If the data contains leading zeroes in some month
instances and leading blanks in others, you must use a non-strict $function.
</blockquote>
 
<tr><th>DD.MM.YY</th>
<td>This is a European six-digit date format for display.
The following SOUL code fragment prints the value <code>OK</code>.
<p class="code">If $sir_date2nd('14.12.94', 'DD.MM.YY') > -9E12 Then
  Print 'OK'
End If
</p>
<blockquote class="note"><b>Note: </b>
With non-strict format matching, such as <var>$Sir_Date2nd</var>,
the leading zero corresponding to
a <var class="term">DD</var> token may be given as a blank, thus allowing " 1.01.00".
With strict matching, however, such a leading blank is not allowed for <var class="term">DD</var>;
a leading blank day value with a strict $function
(that is, one of the <var class="product">Sir2000 User Language Tools Functions</var>) requires the <var class="term">BD</var> token.
If the data contains leading zero days in some
instances and leading blanks in others, you must use a non-strict $function.
</blockquote>
 
<tr><th nowrap>Wkday, DAY Month YYYY "A"T HH:MI</th>
<td>This is a format that could be used for report headers.
The following <var class="product">SOUL</var> code fragment prints a value like <code>Friday, 7February 1998 AT 21:33</code>.
<p class="code">Print $sir_date('Wkday, DAY Month YYYY "A"T HH:MI')
</p>
<blockquote class="note"><b>Note: </b>
<ul>
<li>If an input format contains <code>AM</code> or <code>PM</code>, then the time (<var>HH:MI</var>)
must be between 00:01 and 12:00, and it must be accompanied by either <var>AM</var> or <var>PM</var>.
 
<li>If an input format contains <var>DAY</var> (for example, <var>DAY MON YY</var>) with
non-strict format matching, such as <var>$Sir_Date2nd</var>,
the string matching it may have a leading zero, thus allowing <code>06 MAY 98</code>.
With strict matching $functions (that is, one of the <var class="product">Sir2000 User Language Tools Functions</var>)
however, such a leading zero is not allowed for <var>DAY</var>;
a single digit must be supplied for days 1 through 9.
 
<li>If an input format contains <var>HH</var> with
non-strict format matching, such as <var>$Sir_Date2nd</var>,
the string that matches it may have a leading blank,
thus allowing " 8:30".
With strict matching, however, such a leading blank is not allowed for <var>HH</var>;
a leading blank hour value with a strict $function
(that is, one of the <var class="product">Sir2000 User Language Tools Functions</var>) requires the <var>BH</var> token.
If the data contains leading zero hours in some
instances and leading blanks in others, you must use a non-strict $function.
</ul>
</blockquote>
 
<tr><th>YYIIII</th>
<td>This is a format which could be used for data that contains a two-digit year prefixing other information, such as a sequence number.
The following <var class="product">SOUL</var> code fragment prints the value <code>02</code>:
<p class="code">%d = $sir_date2nd('92ABCD', 'YYIIII')
Print $sir_nd2date(%d + 10*365.25, 'YY')
</p>
<p class="note"><b>Note: </b>
When a pair of format strings are used for transforming date values,
for example for <var>$Sir_Datecnv</var> or processing of updates to <var>SIRFIELD RELATE</var> fields,
both formats must have the same number of <var>I</var> tokens.
The $functions with both an input and output format, for example <var>$Sir_Datecnv</var>,
are only available in the <var class="product">Sir2000 User Language Tools</var>;
<var>SIRFIELD</var> is part of the <var class="product">Sir2000 Field Migration Facility</var>.
</p>
 
<tr><th>YY*</th>
<td>This is a format that could be used for data that contains a two-digit year prefixing other information, such as a sequence number, when the other information is variable length.
The following <var class="product">SOUL</var> code fragment
prints the values <code>OK</code> and <code>OK</code>.
<p class="code">If $sir_date2nd('92', 'YY*') > -9E12 Then
  Print 'OK'
End If
IF $sir_date2nd('1992ABC', 'YYYY*') > -9E12 THEN
  Print 'OK'
End If
</p>
</p>
{{Template:Datetime and format examples}}
<blockquote class="note"><b>Note: </b>
<ul>
<li>At most one occurrence of the asterisk (<var>*</var>) token may appear in a datetime format.
 
<li>When a pair of format strings are used for transforming date values,
for example for <var>$Sir_Datecnv</var> or processing of updates to <var>SIRFIELD RELATE</var> fields,
then if a <var>*</var> token appears in one of the formats,
a <var>*</var> must also appear in the other format.
<p>
The $functions with both an input and output format, for example <var>$Sir_Datecnv</var>,
are only available in the <var class="product">Sir2000 User Language Tools</var>;
<var>SIRFIELD</var> is part of the <var class="product">Sir2000 Field Migration Facility</var>. </p>
</ul>
</blockquote></td></tr>
 
<tr><th>CYYDDD</th>
<td>This is a compact six-digit date format with explicit century information, from 1900 through and including 2899.
The following <var class="product">SOUL</var> fragment prints the value <code>OK</code>:
<p class="code">If $sir_date2nd('097031', 'CYYDDD') > -9E12 Then
  Print 'OK'
End If
</p>
<tr><th>ZYYMMDD</th>
<td>This is a compact six- or seven-digit date format with explicit century information, from 1900 through and including 2899, that can often be used with "old" <var>YYMMDD</var> date values in the 1900s.
The following <var class="product">SOUL</var> fragment prints the values <code>OK</code> and <code>OK</code>:
<p class="code">* Check 1 Dec, 1997:
If $sir_date2nd('971201', 'ZYYMMDD') > -9E12 Then
  Print 'OK'
End If
&#42; Check 1 Dec, 2000:
If $sir_date2nd('1001201', 'ZYYMMDD') > -9E12 Then
  Print 'OK'
End If
</p>
<p class="note"><b>Note: </b>With non-strict format matching (such as <var>$Sir_Date2nd</var>), a three-digit number with a
leading zero may correspond to a <var class="term">ZYY</var> token,
thus allowing "0971201".
With strict matching, however, a three-digit value with a leading zero
is not allowed for <var>ZYY</var>;
a three-digit value less than 100 with a strict $function
(that is, one of the <var class="product">Sir2000 User Language Tools Functions</var>) requires the <var>CYY</var> token.
If the data contains values less than 100 as three digits in some
instances and as two digits in others, you must use a non-strict $function.
</p>
 
<tr><th>YY0000</th>
<td>Decimal digits can be used as separator characters.
The following <var class="product">SOUL</var> fragment prints the value <code>1992NA</code>:
<p class="code">%n = $sir_date2nd('92000', 'YY000')
Print $sir_nd2date(%n, 'YYYY"N"A')
</p>
<p class="note"><b>Note: </b>
Numeric separators, unlike alphabetic separators, do not
need to be preceeded by a double-quote character (<tt>"</tt>). </p>
</td></tr>
</table>


==Datetime error handling==
==Datetime error handling==
<p class="note"><b>Note:</b> For a comparable Fast/Unload discussion, see [[Fast/Unload datetime processing considerations#Datetime error handling|Fast/Unload datetime error handling]]. </p>
<p class="note"><b>Note:</b> For a comparable Fast/Unload discussion, see [[Fast/Unload datetime processing considerations#Datetime error handling|Fast/Unload datetime error handling]]. </p>


Due to an invalid argument value to a datetime function,
Due to an invalid argument value to a datetime function, any of the following errors can occur:
any of the following errors can occur:
<ul>
<ul>
<li>Invalid datetime format specification
<li>Invalid datetime format specification
Line 584: Line 122:
</ul>
</ul>
   
   
One way to detect these errors is to check for the
One way to detect these errors is to check for the appropriate error return value:
appropriate error return value:
<ol>
<ol>
<li>Functions using a numeric value to represent a datetime, and
<li>Functions using a numeric value to represent a datetime, and
Line 593: Line 130:
<li>$Functions (other than <var>$Sir_Date</var>) that only manipulate strings and associated
<li>$Functions (other than <var>$Sir_Date</var>) that only manipulate strings and associated
datetime formats have error return values of a
datetime formats have error return values of a
variable number of asterisks (or, in the case of <var>$Sir_DateDif</var>
variable number of asterisks (or, in the case of <var>$Sir_DateDif</var>,
the value 99,999,999).
the value 99,999,999).
<var>$Sir_DateDif</var> and the date $functions that return a
<var>$Sir_DateDif</var> and the date $functions that return a
variable number of asterisks as error indication,
variable number of asterisks as error indication are available only with the <var class="product">[[Sir2000 User Language Tools]]</var>.
are available only with the <var class="product">Sir2000 User Language Tools</var>.
</ol>
</ol>
   
   
If you are authorized to use the <var class="product">Sir2000 User Language Tools</var>, you can modify the
If you are authorized to use the <var class="product">Sir2000 User Language Tools</var>, you can modify the error detection algorithm so that warning messages or request cancellation occur when a datetime error occurs.
error detection algorithm so that warning messages or request
cancellation occur when a datetime error occurs.
One significant advantage of this product is that you can add
One significant advantage of this product is that you can add
a great deal of error detection to applications without modifying
a great deal of error detection to applications without modifying
any <var class="product">SOUL</var> code.
any <var class="product">SOUL</var> code.
Also, for case 2, above, thorough error detection of error
Also, for case 2, above, thorough error detection of error return values is somewhat complex.
return values is somewhat complex.
   
   
With the <var class="product">Sir2000 User Language Tools</var>,
With the <var class="product">Sir2000 User Language Tools</var>,
you can control the error detection algoritm on a system level, with
you can control the error detection algoritm on a system level, with
user-level and request-level overrides.
user-level and request-level overrides.
These error control features apply to both the <var class="product">Rocket M204</var> date $functions
These error control features apply to both the <var class="product">Rocket M204</var> date and datetime $functions. In addition, all $Sir_Date<i>xxx</i> datetime $functions (excepting <var>$Sir_DateFmt</var>) have an optional error control argument, which allows you to override the error handling for the operation of a single $function call.
and all Sirius datetime $functions; in addition, all Sirius datetime
 
$functions (excepting <var>$Sir_DateFmt</var>) have an optional error control
argument, which allows you to override the error handling for the
operation of a single $function call.
See the <var class="book">[[Media:Ul2krNew.pdf|Sir2000 User Language Tools]]</var> manual for a discussion of the error control features it provides.
==$Sir_Datexxx CENTSPAN argument==
==$Sir_Datexxx CENTSPAN argument==
Many of the [[List of $functions#datetime|$Sir_Datexxx]] functions accept an optional argument
Many of the [[List of $functions#datetime|$Sir_Date<i>xxx</i>]] functions accept an optional argument containing a <var>[[#Using CENTSPAN|CENTSPAN]]</var> value to be used for the call.
containing a <var>[[#Using CENTSPAN|CENTSPAN]]</var> value to be used for the call.
The default value of any <var>CENTSPAN</var> argument is <code>-50</code>, excepting the [[Janus Web Server $functions#Date processing $functions|$Web_Date2<i>xx</i>]] functions without a format argument, in which case the <var>CENTSPAN</var> argument is ignored and a <var>CENTSPAN</var> of <code>1990</code> is used.
The default value of any <var>CENTSPAN</var> argument is -50, excepting
the $Web_Date2xx functions without a format argument, in which
case the <var>CENTSPAN</var> argument is ignored and a <var>CENTSPAN</var> of 1990 is used.
   
   
The default value should be adequate in most cases; if you have
The default value should be adequate in most cases; if you have carefully determined it should be different in some application, code the value on the relevant $function invocations.
carefully determined it should be different in some application,
code the value on the relevant $function invocations.
<var class="product">Rocket M204</var> takes
a different approach to the default: a 100-year period is used.
See the <var class="product">Rocket M204</var> documentation for a
description of the <var>[[CENTSPLT parameter|CENTSPLT]]</var> and <var>[[DEFCENT parameter|DEFCENT]]</var> parameters and $function arguments.
   
   
For a different approach to the default (a 100-year period), see the description of the <var>[[CENTSPLT parameter|CENTSPLT]]</var> and <var>[[DEFCENT parameter|DEFCENT]]</var> parameters and $function arguments.
==Other $functions using date values==
==Other $functions using date values==
In addition to the Sirius datetime functions, which deal only with
In addition to the $Date<i>xxx</i> and $Sir_Date<i>xxx</i> functions, there are some $functions that deal with dates from <var class="product">Model&nbsp;204</var> internal structures.
datetime $functions, there are some
These $functions and any date processing considerations are described below:
$functions that deal with dates from <var class="product">Model 204</var> internal structures.
<table>
These $functions and any date processing considerations are:
<table class="syntaxTable">
<tr><th><var>$FiniTim</var></th>
<tr><th><var>$FiniTim</var></th>
<td>This $function returns the file initialization date and time, using a two-digit year. </td></tr>
<td>This $function returns the file initialization date and time, using a two-digit year. </td></tr>
Line 675: Line 192:
</table>
</table>


==Benefits of Sirius datetime processing==
==<b id="benefits"></b>Benefits of $Sir_Date<i>xxx</i> datetime processing==
Following is a list of benefits offered by Sirius datetime processing.
<p>
To provide concrete comparisons, there
Following is a list of benefits offered by $Sir_Date<i>xxx</i> (and Sir2000) datetime processing. To provide concrete comparisons, there
are some references to the standard <var class="product">Model 204</var> date $functions provided
are some references to the standard <var class="product">Model&nbsp;204</var> date $functions provided by <var class="product">Rocket&nbsp;M204</var>. </p>
by <var class="product">Rocket M204</var>.
<p>
For Fast/Unload specific remarks, see [[Fast/Unload datetime processing considerations#Benefits of Fast/Unload datetime processing|Benefits of Fast/Unload datetime processing]].
</p>
{{Template:Benefits of $Sir Datexxx datetime processing}}


For Fast/Unload specific remarks, see [[Fast/Unload datetime processing considerations#Benefits of Fast.2FUnload datetime processing|Benefits of Fast/Unload datetime processing]].
[[Category: SOUL]]
 
<table class="thJustBold">
<tr><th>SPANSIZE</th>
<td>The <var>SPANSIZE</var> processing creates a very strong barrier to detecting otherwise unnoticed two-digit year processing errors. This is unique to Sirius datetime processing. </td></tr>
 
<tr><th>Relative CENTSPAN</th>
<td>The relative <var>CENTSPAN</var> specification (for example, "-50")
let you maintain a flexible "rolling" window for two-digit year processing. </td></tr>
 
<tr><th>Default CENTSPAN</th>
<td>One significant advantage of a relative <var>CENTSPAN</var> is that it allows the default (<code>1990</code> for <var>$Web_Date2xx</var> functions without a format, and <code>-50</code> otherwise) of a reasonable value without parameter changes in all batch and online jobs. </td></tr>
 
<tr><th>Format tokens</th>
<td>There is a very large set of tokens in the Sirius datetime formats. For example, there are four different tokens representing the day of the week, and time of day can be represented.
 
<var class="product">Rocket M204</var> date formats do not have any day-of-week nor time-of-day tokens, and other <var class="product">Rocket M204</var> token variations, for example, <var class="term">CYY</var> vs. <var class="term">ZYY</var>, is done by a complex argument setting. </td></tr>
 
<tr><th>Pattern match tokens</th>
<td>The Sirius datetime formats can contain single-character (I) or variable-length character (*) match-any tokens in datetime formats. For example, you can specify that a string has an imbedded year, and you can process that year as a date. </td></tr>
 
<tr><th>Format-free representations</th>
<td>Non-string datetime values allow you to pass around dates simply as numbers, without the complexities of carrying the corresponding string format (you only need to establish the scale to operate on a value). </td></tr>
 
<tr><th nowrap>Operating on numeric representations</th>
<td>Numeric date values can be operated on directly with <var class="product">User Language</var>, especially allowing you to add datetime differences (for example, <code>+</code>), rather than calling a DATECHG $function and providing a format. </td></tr>
 
<tr><th>Time</th>
<td>All Sirius datetime $functions allow any reference to a "date" to include time of day. The only <var class="product">Rocket M204</var> datetime $function that provides a time of day is $TIME, the current time of day, in one fixed format. </td></tr>
 
<tr><th>$Sir_Date formats</th>
<td><var>$Sir_Date</var> allows you to specify any format to return the current date and time; <var>$DATE</var> has only a few numeric codes for a few formats. </td></tr>
 
<tr><th>Error control args</th>
<td>The <var class="product">Sir2000 User Language Tools</var> provides error handling control that applies to all datetime $functions &mdash; Sirius and <var class="product">Rocket M204</var>. Additionally, all Sirius datetime $functions (except <var>[[$Sir_DateFmt]]</var>, of course) allow you to specify it for a single $function invocation. </td></tr>
 
<tr><th nowrap>Error values of numeric date $functions</th>
<td>The $functions that use non-string datetime values provide very uniform error return values: -9.E12 or a null string for numeric or string result $functions, respectively. </td></tr>
</table>

Latest revision as of 19:41, 20 April 2018

The discussion of datetime processing on this page applies to $functions, #functions, and commands added to the Rocket M204 products in version 7.5 of Model 204. These added functions and commands share "datetime formats" that differ from those in the pre-7.5 standard Model 204 related date $functions.

Although the discussion here tends to emphasize the SOUL $Sir_Datexxx $functions, these datetime format strings are used in several Rocket Model 204 add-on products, including:


Datetime formats

The string representation of a date is described by a datetime format. This value is a character string, composed of the concatenation of these:

  • Tokens

    For example, YYYY for a four-digit year, and MI for minutes.

  • Separator characters

    For example, forward slash (/) in MM/DD/YY for two-digit month, day, and year separated by slashes.

These datetime format strings are used in many methods, commands, and $functions. Since all methods that operate on strings are functions, methods and $functions are collectively referred to as functions on this page.

The rules for these datetime format strings are consistent wherever these formats are used, though certain uses of these strings might impose extra restrictions. For example, a leading blank may match an HH, DD, or MM token in SOUL methods, but it may not in some cases in other Rocket M204 products.

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.
DDTwo-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.

Valid datetimes

For a datetime string to be valid it must meet the following criteria:

  • Its length must be less than 128 characters.
  • It must be compatible with its corresponding format string.
  • It must represent a valid date and/or time.

    For example, at most 23:59:59.999 for a time, 01-12 for a month, 01-31 or less (depending on the month) for a day, February 29 is only valid in leap years (only centuries divisible by 4 are leap years: 2000 is but neither 1800, 1900, nor 2100 are).

    Note: weekdays are not checked for consistency against the date. For example, both Saturday, 02/15/97 and Friday, 02/15/97 are valid.

  • It must be within the date range allowed for the corresponding format.

    A datetime string used with a CYY or ZYY format can only represent dates from 1900 to 2899, inclusive. Except for SirDBA, a datetime string used with a YY format can only represent dates in a range of 100 or less years, as determined by CENTSPAN and SPANSIZE. The valid range of dates for all other formats is from 1 January 1753 thru 31 December 9999.

Processing dates with two-digit year values

A date field with only two digits for the year value is capable of representing a range of up to one hundred years. When we compare a pair of two-digit year values, we are accustomed to thinking of the century as fixed, so that all dates are either 19xx or 20xx. However, a date field with two-digit year values can actually represent dates from two different centuries, provided that the range of dates does not exceed 100 years.

Using CENTSPAN

CENTSPAN provides a mechanism for unambiguously converting dates with two-digit year values into dates with four-digit year values. The CENTSPAN mechanism allows two-digit year values to span two centuries without confusion. CENTSPAN identifies the four-digit year value that is the start of a range of years represented by the two-digit year values.

CENTSPAN may be specified as an absolute unsigned four-digit value between 1753 and 9999, or it may be specified as a relative signed value between -99 and +99, inclusive. A relative CENTSPAN value is dynamically converted to an effective absolute value before it is used to perform a YY to YYYY conversion. The effective CENTSPAN value is formed by adding the relative CENTSPAN to the current four-digit year value at the time the relative value is converted.

A simple algorithm is used to convert a two-digit year value (YY) to a four-digit year value, using a four-digit absolute or effective CENTSPAN value (HHLL). If the two-digit year value is less than the low-order two digits of the CENTSPAN value, then the resulting century is one greater than the high-order two digits of the CENTSPAN value. Otherwise the resulting century is the same as the high-order two digits of the CENTSPAN value.

Using all one hundred available years for mapping two-digit year values can cause significant confusion and result in data integrity errors. This is because dates just above and just below the 100-year window are mapped to the other end of the window. From the previous example, the date "47" will be interpreted as 1947, when it could have conceivably been 2047. Simlarly, the date "46" will be interpreted as 2046, when it might have been 1946.

If CENTSPAN is set to a value that is too high, dates that are just prior to CENTSPAN will appear to occur 100 years hence. If CENTSPAN is set to a value that is too low, dates that fall just after CENTSPAN+99 will appear to have occurred 100 years earlier. A full one-hundred year window also can not detect attempts to represent more than one hundred years of values with a two-digit year.

Using SPANSIZE

To protect from the ambiguities that can occur at each end of the 100-year window defined by CENTSPAN, SPANSIZE is used to restrict the size of the window used for mapping two-digit year values. The effect is to create two guard bands', one just below the date window and one just above. An attempt to represent a date value that lands in a guard band produces an error.

Each guard band contains CENTSPAN - SPANSIZE years, hence a SPANSIZE of 100 removes the protection. The default SPANSIZE is a value that you can customize in your load module. If you do not customize it, the value of SPANSIZE is 90, which provides protection for two ten-year windows: one below the CENTSPAN setting and one starting at CENTSPAN+90. From the previous example:

An attempt to represent the values "37" through "46" is rejected. This protects the range 1937 through 1946 as well as the range 2037 through 2046. Note that an intended value of 2047, expressed as "47" will be accepted and interpreted as 1947. In general a smaller SPANSIZE provides the highest assurance of correct mappings. However, any setting of SPANSIZE less than 100 will probably detect the case where a range greater than one hundred years is being used.

Strict and non-strict format matching

As mentioned above in Datetime formats, 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.

The types of strict matching are as follows:

Alpha tokens For alphabetic tokens (for example, Month), a strict match requires the input value to be the correct case. For example, the MON token is strictly matched by JAN but not by Jan, and the reverse is true for the Mon token. For non-strict matching, the alphabetic tokens are matched by any combination of uppercase and lowercase input.
HH, MM, DD For these tokens, a strict match requires a leading zero for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading blank followed by a single numeric digit.
BH, BM, BD For these tokens, a strict match requires a leading blank for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading zero followed by a numeric digit.
DAY For this token, a strict match requires a single digit for values less than 10. For non-strict matching, a value less than 10 can also be represented by a leading zero followed by a numeric digit.
ZYY For this token, a strict match requires two digits for values less than 100. For non-strict matching, a value less than 100 can also be represented by a leading zero followed by a two numeric digits.

The strict functions are only available in the Sir2000 User Language Tools. With non-strict date $functions, however, you can use the following technique to check a datetime string using strict rules:

If date Eq '' Or date NE $Sir_NM2Date(- $Sir_Date2NM(date, fmt), fmt) Then error handling End If

Or, using SOUL OO functions:

if date eq '' or date ne date:stringToMilliseconds(format):millisecondsToString(format) then error handling end if

Datetime and format examples

The extensive set of format tokens is shown above in Datetime format tokens. These tokens and the various separator characters can be combined in almost limitless possibility, giving rise to an extremely large set of datetime formats.

This section provides examples of some common datetime formats, and also tries to explain the use of some of the format tokens that might not be obvious. It also has examples for formats whose usage differs depending on whether strict or non-strict format matching is involved.

Each example format is explained and also presented with some matching datetimes; again, bear in mind that these tokens can be combined in very many ways and only a very few are shown here. It is assumed that these examples are invoked sometime between the years 1998-2040, as the basis for relative CENTSPAN calculations.

Note: For comparable Fast/Unload examples, see Fast/Unload datetime and format examples.

YYMMDD This is the common six-digit date format which supports sort order if all dates are within a single century.

The following SOUL code fragment prints the value OK:

If $sir_date2nd('960229', 'YYMMDD') > -9E12 Then Print 'OK' End If

For Sir2000 User Language Tools users, the following statement prints the value 1:

print $sir_datechk('YYMMDD', '960229')

YYYYMMDD This is the common eight-digit date format which supports sort order with dates in two centuries.

The following SOUL code fragment prints the value 19921212.

%n = $sir_date2nd('921212', 'YYMMDD') Print $sir_nd2date(%n, 'YYYYMMDD')

For Sir2000 User Language Tools users, the following statement prints the value 19921212:

print $sir_datecnv('YYMMDD', 'YYYYMMDD', '921212')

MM/DD/YY This is the U.S. six-digit date format for display. The value 12/14/97 matches this format.

The following SOUL code fragment prints the value OK:

If $sir_date2nd('12/14/94', 'MM/DD/YY') > -9E12 Then Print 'OK' End If

For Sir2000 User Language Tools users, the following statement prints the value 1:

print $sir_datechk('MM/DD/YY', '12/14/94')

Note: With non-strict format matching, such as $Sir_Date2nd, the leading zero corresponding to an MM token may be given as a blank, thus allowing " 7/15/98".

With strict matching, however, such a leading blank is not allowed for MM. A leading blank month value with a strict $function (that is, one of the Sir2000 User Language Tools $functions) requires the BM token.

If the data contains leading zeroes in some month instances and leading blanks in others, you must use a non-strict $function.

DD.MM.YY This is a European six-digit date format for display. The value 14.12.97 matches this format.

The following SOUL code fragment prints the value OK:

If $sir_date2nd('14.12.94', 'DD.MM.YY') > -9E12 Then Print 'OK' End If

For Sir2000 User Language Tools users, the following statement prints the value 1:

print $sir_datechk('DD.MM.YY', '14.12.94')

Note: With non-strict format matching, such as $Sir_Date2nd, the leading zero corresponding to a DD token may be given as a blank, thus allowing " 1.01.00".

With strict matching, however, such a leading blank is not allowed for DD. A leading blank day value with a strict $function (that is, one of the Sir2000 User Language Tools Functions) requires the BD token.

If the data contains leading zero days in some instances and leading blanks in others, you must use a non-strict $function.

Wkday, DAY Month YYYY "A"T HH:MI This is a format that could be used for report headers.

The following SOUL statement prints a value like Friday, 7 February 1998 AT 21:33:

print $sir_date('Wkday, DAY Month YYYY "A"T HH:MI')

Note:

  • If an input format contains AM or PM, then the time (HH:MI) must be between 00:01 and 12:00, and it must be accompanied by either AM or PM.
  • If an input format contains DAY (for example, DAY MON YY) with non-strict format matching, such as $Sir_Date2nd, the string matching it may have a leading zero, thus allowing 06 MAY 98.

    With strict matching $functions (that is, one of the Sir2000 User Language Tools $functions) however, such a leading zero is not allowed for DAY; a single digit must be supplied for days 1 through 9.

  • If an input format contains HH with non-strict format matching, such as $Sir_Date2nd, the string that matches it may have a leading blank, thus allowing " 8:30".

    With strict matching, however, such a leading blank is not allowed for HH; a leading blank hour value with a strict $function requires the BH token.

    If the data contains leading zero hours in some instances and leading blanks in others, you must use a non-strict $function.

YYIIII This format might be used for data that contains a two-digit year prefixing other information, such as a sequence number. The value 92A123 matches this format.

The following SOUL code fragment prints the value 02:

%d = $sir_date2nd('92ABCD', 'YYIIII') Print $sir_nd2date(%d + 10*365.25, 'YY')

For Sir2000 User Language Tools users, the following statement prints the value 1992ABCD:

print $sir_datecnv('YYIIII', 'YYYYIIII', '92ABCD')

Note: When a pair of format strings are used for transforming date values, for example for $Sir_DateCnv or processing of updates to SIRFIELD RELATE fields, both formats must have the same number of I tokens. The $functions with both an input and output format, for example $Sir_DateCnv, are only available in the Sir2000 User Language Tools; SIRFIELD is part of the Sir2000 Field Migration Facility.

YY* This format might be used for data that contains a two-digit year prefixing other information, such as a sequence number, when the other information is variable length. The values 92 and 92ABC123 match this format.

The following SOUL code fragment prints the values OK and OK:

If $sir_date2nd('92', 'YY*') > -9E12 Then Print 'OK' End If IF $sir_date2nd('1992ABC', 'YYYY*') > -9E12 THEN Print 'OK' End If

For Sir2000 User Language Tools users, the following statements print the values 1992 and 1992XYZ:

print $sir_datecnv('YY*', 'YYYY*', '92') print $sir_datecnv('YY*', 'YYYY*', '92XYZ')

Note:

  • At most one occurrence of the asterisk (*) token may appear in a datetime format.
  • If a pair of format strings are used for transforming date values, for example for $Sir_DateCnv or processing of updates to SIRFIELD RELATE fields, then if a * token appears in one of the formats, a * must also appear in the other format.

    The $functions with both an input and output format, for example $Sir_DateCnv, are only available in the Sir2000 User Language Tools; SIRFIELD is part of the Sir2000 Field Migration Facility.

CYYDDD This is a compact six-digit date format with explicit century information, from 1900 through and including 2899. The value 097031 (representing 31 January 1997) matches this format.

The following SOUL fragment prints the value OK:

If $sir_date2nd('097031', 'CYYDDD') > -9E12 Then Print 'OK' End If

For Sir2000 User Language Tools users, the following statement prints the value 1:

print $sir_datechk('CYYDDD', '097031')

ZYYMMDD This is a compact six- or seven-digit date format with explicit century information, from 1900 through and including 2899, that can often be used with "old" YYMMDD date values in the 1900s. The values 970501 (representing 1 May 1997) and 1000501 (representing 1 May 2000) match this format.

The following SOUL fragment prints the values OK and OK:

* Check 1 Dec, 1997: If $sir_date2nd('971201', 'ZYYMMDD') > -9E12 Then Print 'OK' End If * Check 1 Dec, 2000: If $sir_date2nd('1001201', 'ZYYMMDD') > -9E12 Then Print 'OK' End If

For Sir2000 User Language Tools users, the following statements print the values 1 and 1:

* Check 1 Dec, 1997: print $sir_datechk('ZYYMMDD', '971201') * Check 1 Dec, 2000: print $sir_datechk('ZYYMMDD', '1001201')

Note: With non-strict format matching (such as $Sir_Date2nd), a three-digit number with a leading zero may correspond to a ZYY token, thus allowing "0971201".

With strict matching, however, a three-digit value with a leading zero is not allowed for ZYY. A three-digit value less than 100 with a strict $function (that is, one of the Sir2000 User Language Tools Functions) requires the CYY token.

If the data contains values less than 100 as three digits in some instances and as two digits in others, you must use a non-strict $function.

YY0000 Decimal digits can be used as separator characters. The value 980000 matches this format.

The following SOUL fragment prints the value 1992NA:

%n = $sir_date2nd('92000', 'YY000') Print $sir_nd2date(%n, 'YYYY"N"A')

For Sir2000 User Language Tools users, the following SOUL statement prints the value 1992NA:

Print $sir_datecnv('YY0000', 'YYYY"N"A', '920000')

Note: Numeric separators, unlike alphabetic separators, do not need to be preceded by a double-quote character (").

Datetime error handling

Note: For a comparable Fast/Unload discussion, see Fast/Unload datetime error handling.

Due to an invalid argument value to a datetime function, any of the following errors can occur:

  • Invalid datetime format specification
  • Datetime string not matching format
  • Datetime out of range for the format
  • Invalid CENTSPAN value
  • Datetime out of range for CENTSPAN/SPANSIZE combination

One way to detect these errors is to check for the appropriate error return value:

  1. Functions using a numeric value to represent a datetime, and $Sir_Date, have error return values of -9.E12 or a null string for numeric or string result functions, respectively.
  2. $Functions (other than $Sir_Date) that only manipulate strings and associated datetime formats have error return values of a variable number of asterisks (or, in the case of $Sir_DateDif, the value 99,999,999). $Sir_DateDif and the date $functions that return a variable number of asterisks as error indication are available only with the Sir2000 User Language Tools.

If you are authorized to use the Sir2000 User Language Tools, you can modify the error detection algorithm so that warning messages or request cancellation occur when a datetime error occurs. One significant advantage of this product is that you can add a great deal of error detection to applications without modifying any SOUL code. Also, for case 2, above, thorough error detection of error return values is somewhat complex.

With the Sir2000 User Language Tools, you can control the error detection algoritm on a system level, with user-level and request-level overrides. These error control features apply to both the Rocket M204 date and datetime $functions. In addition, all $Sir_Datexxx datetime $functions (excepting $Sir_DateFmt) have an optional error control argument, which allows you to override the error handling for the operation of a single $function call.

$Sir_Datexxx CENTSPAN argument

Many of the $Sir_Datexxx functions accept an optional argument containing a CENTSPAN value to be used for the call. The default value of any CENTSPAN argument is -50, excepting the $Web_Date2xx functions without a format argument, in which case the CENTSPAN argument is ignored and a CENTSPAN of 1990 is used.

The default value should be adequate in most cases; if you have carefully determined it should be different in some application, code the value on the relevant $function invocations.

For a different approach to the default (a 100-year period), see the description of the CENTSPLT and DEFCENT parameters and $function arguments.

Other $functions using date values

In addition to the $Datexxx and $Sir_Datexxx functions, there are some $functions that deal with dates from Model 204 internal structures. These $functions and any date processing considerations are described below:

$FiniTim This $function returns the file initialization date and time, using a two-digit year.
$ListSrt This $function sorts a $list; it allows you to specify a C modifier of the sort key to indicate a two-digit year, which it will then sort using a CENTSPAN of 1975.
$PrcLEx or $PrcLExG These $functions retrieve a $list of information about procedures in file or group.
  • The last-modified date is retrieved as a two-digit year; to retrieve it as a four-digit year, use $Proc_List or $Proc_ListG.
  • You may sort the $list using a two-digit year if you use the C modifier of $ListSrt.
  • The fourth argument, which specifies the last-modified date selection criterion, is passed as a two-digit year, using a CENTSPAN of 1975.
$Proc_List or $Proc_ListG These $functions retrieve a $list of information about procedures in file/group.
  • The last-modified date is retrieved as a four-digit year.
  • The fourth argument, which specifies the last-modified date selection criterion, is passed as a two-digit year, using a CENTSPAN of 1975.
$SirJGet This $function retrieves audit trail data into a $list.

The arguments that specify the start and end time to extract

are passed as two-digit years, using a CENTSPAN of 1990.
$SirTime This $function returns the current date and time, using a two-digit year.

Benefits of $Sir_Datexxx datetime processing

Following is a list of benefits offered by $Sir_Datexxx (and Sir2000) datetime processing. To provide concrete comparisons, there are some references to the standard Model 204 date $functions provided by Rocket M204.

For Fast/Unload specific remarks, see Benefits of Fast/Unload datetime processing.

SPANSIZE The SPANSIZE processing creates a very strong barrier to detecting otherwise unnoticed two-digit year processing errors.
Relative CENTSPAN The relative CENTSPAN specification (for example, -50) lets you maintain a flexible "rolling" window for two-digit year processing.
Default CENTSPAN One significant advantage of a relative CENTSPAN is that it allows the default (1990 for $Web_Date2xx functions without a format, and -50 otherwise) of a reasonable value without parameter changes in all batch and Online jobs.
Format tokens There is a very large set of tokens in the Sir2000 and $SirDatexxx datetime formats. For example, there are four different tokens representing the day of the week, and time of day can be represented. Standard SOUL date formats do not have any day-of-week or time-of-day tokens, and other standard SOUL token variations, for example, CYY versus ZYY, are done by a complex argument setting.
Pattern match tokens The Sir2000 and $SirDatexxx datetime formats can contain single-character (I) or variable-length character (*) match-any tokens in datetime formats. For example, you can specify that a string has an embedded year, and you can process that year as a date.
Format-free representations Non-string datetime values allow you to pass around dates simply as numbers, without the complexities of carrying the corresponding string format (you only need to establish the scale to operate on a value).
Operating on numeric representations Numeric date values can be operated on directly with SOUL, especially allowing you to add datetime differences (for example, +), rather than calling a $DateChg $function and providing a format.
Time All Sir2000 and $SirDatexxx datetime $functions allow any reference to a "date" to include time of day. The only standard SOUL datetime $function that provides a time of day is $Time, the current time of day, in one fixed format.
$Sir_Date formats $Sir_Date lets you specify any format to return the current date and time. $Date has only a few numeric codes for a few formats.
Error control arguments Sir2000 User Language Tools provides error handling control that applies to all datetime $functions, including standard SOUL. Additionally, the Sir2000 and $SirDatexxx datetime $functions (except $Sir_DateFmt) let you specify error control for a single $function invocation.
Error values of numeric date $functions The $functions that use non-string datetime values provide very uniform error return values: -9.E12 or a null string, for numeric or string result $functions, respectively.