Fast/Unload datetime processing considerations: Difference between revisions

From m204wiki
Jump to navigation Jump to search
(Automatically generated page update)
 
m (link repair)
 
(21 intermediate revisions by 3 users not shown)
Line 1: Line 1:
<!-- Page name: Fast/Unload datetime processing considerations-->
<!-- Page name: Fast/Unload datetime processing considerations-->
<p></p>
This page presents date processing issues, including usage of
This chapter presents date processing issues, including usage of
<var class="product">Fast/Unload</var> past the year 1999, an explanation of its processing of dates, and any rules and restrictions you must follow to achieve correct
<var class="product">Fast/Unload</var> past the year 1999, an explanation of its processing of dates,
and any rules and restrictions you must follow to achieve correct
results using date values with <var class="product">Fast/Unload</var>.
results using date values with <var class="product">Fast/Unload</var>.
<p></p>
 
==Overview==
<var class="product">Fast/Unload</var> uses dates in the following ways:
<var class="product">Fast/Unload</var> uses dates in the following ways:
<ul>
<ul>
<li>To examine the CPU clock
<li>To examine the CPU clock (as returned by the STCK hardware instruction) to determine the
(as returned by the STCK hardware instruction) to determine the
current date, in case <var class="product">Fast/Unload</var> is under a rental or trial agreement</li>
current date, in case <var class="product">Fast/Unload</var> is under a rental or trial agreement
 
<li>As arguments to various #functions, and returned values
<li>As arguments to various #functions, and returned values from them </li>
from them
</ul>
</ul>
Please note that in addition to the above date processing performed by <var class="product">Fast/Unload</var>,
 
it also unloads <var class="product">Model 204</var> files and allows manipulation of other values which
In addition to the above date processing performed by <var class="product">Fast/Unload</var>,
might contain two-digit year date values.
it also unloads <var class="product">Model 204</var> files and allows manipulation of other values that might contain two-digit year date values.
The customer must ensure
You must ensure that any application using that data has an algorithm or rule for unambiguously
that any application using that data has an algorithm or rule for unambiguously
determining the correct century for the values.
determining the correct century for the values.
<p></p>
 
For example, the UAI statement with the SORT clause allows you to sort by
For example, the <var>UAI</var> statement with the <var>SORT</var> clause allows you to sort by
a <var class="product">Model 204</var> field; if you are sorting by a two-digit year date field, you need
a <var class="product">Model 204</var> field. If you are sorting by a two-digit year date field, you need to supply information to enable the sort program to determine the century.
to supply information to enable the sort program to determine the century.
You can do this using the <var>FORMAT</var> keyword in the <var>UAI SORT</var> items, as described in [[Fast/Unload Extraction Language (FUEL)#uai|UNLOAD ALL INFORMATION or UAI]].
You can do this using the FORMAT keyword in the UAI SORT items, as described
 
in [[#uai|UNLOAD ALL INFORMATION or UAI]].
For headers on pages or rows that occur on printed pages or displayed screens,
<p></p>
For headers on pages or rows that occur on
printed pages or displayed screens,
Rocket Software products generally use a full four-digit
Rocket Software products generally use a full four-digit
year format, although they may display dates with two-digit
year format, although they may display dates with two-digit
years in circumstances where the proper century can be
years in circumstances where the proper century can be
inferred from the context.
inferred from the context.
<p></p>
 
You must examine all uses of date values in your applications to ensure that
You must examine all uses of date values in your applications to ensure that
each of your applications produces correct results.
each of your applications produces correct results.
Line 39: Line 33:
correctly process and transmit dates beyond 1999 in order for
correctly process and transmit dates beyond 1999 in order for
<var class="product">Fast/Unload</var> to operate properly.
<var class="product">Fast/Unload</var> to operate properly.
<p></p>
 
Most <var class="product">Fast/Unload</var> date processing involves the use of datetime #functions.
Most <var class="product">Fast/Unload</var> date processing involves the use of datetime #functions. Occasionally, these are referred to as the "#DATExxx" functions; this is
Occasionally, we refer to the "#DATExxx" functions; this is
meant to also include <var>[[#TIME: Current time and/or date|#TIME]]</var> and the <var>#N<i>xxx</i>2DATE</var> functions.
meant to also include #TIME and the #Nxxx2DATE functions.
 
<p></p>
In operational terms, there are two classes of datetime #functions:
In operational terms, there are two classes of datetime #functions:
<ol>
<ul>
<li>#Functions using a numeric value to represent a datetime,
<li>#Functions using a numeric value to represent a datetime,
where 0 represents 12:00 AM, 1 January 1900; for example,
where 0 represents <code>12:00 AM, 1 January 1900</code>. For example,
<nowiki>#DATE2NM</nowiki> and #NM2DATE (number of milliseconds since
<var>[[#DATE2NM: Convert datetime string to number of milliseconds|#DATE2NM]]</var> and <var>[[#NM2DATE: Convert number of milliseconds to datetime string|#NM2DATE]]</var> (number of milliseconds since
the start of 1900).
the start of 1900).
<p></p>
<p>
These #functions perform <b>non-strict</b> matching of date strings to date
These #functions perform <b>non-strict</b> matching of date strings to date
formats; for example, a leading blank is allowed for the HH
formats. For example, a leading blank is allowed for the HH token.</p></li>
token.
 
<li>Other #functions that only manipulate strings and associated
<li>Other #functions that only manipulate strings and associated
datetime formats;
datetime formats. For example, <var>[[#DATECHG: Add some days to datetime|#DATECHG]]</var> (add number of days to given date).
for example, #DATECHG (add number of days to given date).
<p>
<p></p>
These #functions perform <b>strict</b> matching of date strings to date
These #functions perform <b>strict</b> matching of date strings to date
formats; for example, a leading blank is <b>not</b> allowed
formats. For example, a leading blank is <b>not</b> allowed
for the HH token.
for the HH token.
These #functions generally produce the same results as the same
These #functions generally produce the same results as the same
SOUL $DATExxx functions, with additional enhancements.
SOUL $DATExxx functions, with additional enhancements.</p></li>
</ol>
</ul>
 
See [[#strict|Strict and non-strict format matching]] for a discussion of strict and non-strict
See [[#strict|Strict and non-strict format matching]] for a discussion of strict and non-strict
format matching, including a technique for accomplishing strict
format matching, including a technique for accomplishing strict
date checking using the non-strict #functions.
date checking using the non-strict #functions.
<p></p>
 
Notes:
Notes:
<ul>
<ul>
<li>All #DATExxx functions that can have argument errors
<li>All #DATExxx functions that can have argument errors
(that is, all #functions except #DATEFMT) accept an optional "return
(that is, all #functions except <var>[[#DATEFMT: Validate datetime format string|#DATEFMT]]</var>) accept an optional "return code" argument.
code" argument.
If an argument error occurs and the return code argument is absent,
If an argument error occurs and the return code argument is absent,
<var class="product">Fast/Unload</var> terminates; if the return code argment is present, an error will
<var class="product">Fast/Unload</var> terminates; if the return code argument is present, an error will set the return code to a non-zero number and the result of the #function
set the return code to a non-zero number and the result of the #function
is the <var>MISSING</var> value.
is the MISSING value.
<p>
<p></p>
The SOUL [[SOUL_$functions#datex|$DATExxx]] and [[SOUL_$functions#datetime|$SIR_DATExxx]]
The User Language $DATExxx and $SIR_DATExxx
functions take a different approach to error handling.
functions take a different approach to error handling;
Each uses a special return value (or class of values) to indicate an
each uses a special return value (or class of values) to indicate an
argument error.</p></li>
argument error.
 
<li>The default format for #DATE is "YYYY-MM-DD";
<li>The default format for <var>#DATE</var> is <var>YYYY-MM-DD</var>;
the default for $DATE and $SIR_DATE is "YY-MM-DD".
the default for <var>[[$Date]]</var> and <var>[[$Sir_Date]]</var> is <var>YY-MM-DD</var>.</li>
</ul>
</ul>
<p></p>
 
The rest of this chapter contains a discussion of datetime
The rest of this page contains a discussion of datetime formats, valid datetime strings,
formats, valid datetime strings,
processing of two-digit year values, and datetime error handling.
processing of two-digit year values, and datetime error handling.
It also contains example datetime formats and corresponding example
It also contains example datetime formats and corresponding example datetime strings.
datetime strings.
Finally, there is a list of benefits of <var class="product">Fast/Unload</var> datetime processing.
Finally, there is a list of benefits of <var class="product">Fast/Unload</var> datetime processing.
   
   
<div id="dtfmt"></div>
==<b id="dtfmt"></b>Datetime formats==
==Datetime formats==
The representation of a date is determined by a <b>datetime format</b>.
<!--Caution: <div> above-->
This value is a character string, composed of the concatenation of these:
<ul>
The representation of a date is determined by a <i>datetime format</i>.
<li>Tokens
This value is a character string, composed of the concatenation of
<p>For example, <var>YYYY</var> for a four-digit year, and <var>MI</var> for
tokens (for example, "YYYY" for a four-digit year, and "MI" for
minutes.</p></li>
minutes) and separator characters
 
(for example, "/" in
<li>Separator characters
"MM/DD/YY" for two-digit month, day, and year separated by
<p>
slashes).
For example, forward slash (<tt>/</tt>) in
<p></p>
<var>MM/DD/YY</var> for two-digit month, day, and year separated by
These <i>datetime format</i> strings are used in many
slashes.</p></li>
products in addition to <var class="product">Fast/Unload</var>.
</ul>
The products using datetime format strings are:
 
These datetime format strings are used in several Rocket Model&nbsp;204 add-on products in addition to <var class="product">Fast/Unload</var>.
The additional products using datetime format strings are:
<ul>
<ul>
<li><var class="product">Fast/Unload</var>
<li><var class="product">[[Media:JoclrNew.pdf|Janus Open Client]]</var> </li>
<li><var class="product">Janus Open Client</var>
 
<li><var class="product">Janus Open Server</var>
<li><var class="product">[[Media:JosrvrNew.pdf|Janus Open Server]]</var>
<li> <var class="product">Janus Specialty Data Store</var>
</li>
<li><var class="product">Janus Web Server</var>
 
<li><var class="product">SirDBA</var>
<li><var class="product">[[Media:JsdsrNew.pdf|Janus Specialty Data Store]]</var> </li>
<li><var class="product">Sirius Functions</var>
 
<li><var class="product">Sir2000 Field Migration Facility</var>
<li><var class="product">[[Janus Web Server]]</var> </li>
<li><var class="product">Sir2000 User Language Tools</var>
 
<li><var class="product">[[SirDBA]]</var> </li>
 
<li><var class="product">[[Release notes for Model 204 version 7.5#Former Sirius $functions|Sirius Functions]]</var> </li>
 
<li><var class="product">[[Media:SirfieldNew.pdf|Sir2000 Field Migration Facility]]</var>
 
<li><var class="product">[[Sir2000 User Language Tools]]</var> </li>
</ul>
</ul>
<!-- Note: all the above products imbed COMDATE except Janus Web -->
<!-- Note: all the above products imbed the SCRIPT file COMDATE except Janus Web -->
The rules for these <i>datetime format</i> strings are consistent
The rules for these datetime format strings are consistent
throughout all these products, though certain uses of these strings
throughout all these products, though certain uses of these strings
might impose extra restrictions.
might impose extra restrictions.
For example, a leading blank is allowed for the HH, DD, and MM parts
For example, a leading blank is allowed for the <var>HH</var>, <var>DD</var>, and <var>MM</var> parts
of a date argument using a non-strict date #function, such as
of a date argument using a non-strict date #function, such as
<nowiki>#DATE2NS</nowiki>, but is not allowed for the strict date
<var>[[Fast/Unload standard functions##DATE2NS: Convert datetime string to number of seconds|#DATE2NS]]</var>, but is not allowed for the strict date
<nowiki>#functions</nowiki>.
<nowiki>#functions</nowiki>.
<p></p>
 
There are certain rules applied to determine if a format
===Basic rules===
is valid.
There are certain rules applied to determine if a format is valid.
The basic rules are:
The basic rules are:
<ol>
<ol>
<li>If a format string contains a numeric datetime token (that is
<li>If a format string contains a numeric datetime token (that is
"ND", "NM", or "NS"), then the
<var>ND</var>, <var>NM</var>, or <var>NS</var>), the
format string must consist of only one token.
format string must consist of only one token.
Numeric datetime tokens are only supported in format strings for
Numeric datetime tokens are only supported in format strings for
the <var class="product">Sir2000 Field Migration Facility</var>.
the <var class="product">Sir2000 Field Migration Facility</var>. </li>
<li>You must specify at least one time, weekday, or date
 
token.
<li>You must specify at least one time, weekday, or date token. </li>
 
<li>Except for "weekday", you can't specify redundant information.
<li>Except for "weekday", you can't specify redundant information.
More specifically this means
More specifically this means:
<ul>
<ul>
<li>Except for "I", no token can be specified twice.
<li>Except for <var>I</var>, no token can be specified twice. </li>
<li>At most one year format (contains Y) can be specified.
 
<li>At most one month format (contains MON, Mon, or MM) can be specified.
<li>At most one year format (contains <var>Y</var>) can be specified. </li>
<li>At most one day format (DD or Day) can be specified.
 
<li>At most one weekday format (WKD, Wkd, WKDAY, or Wkday) can be specified.
<li>At most one month format (contains <var>MON</var>, <var>Mon</var>, or <var>MM</var>) can be specified. </li>
<li>If AM is specified, then PM can not be specified.
 
<li>At most one fractions-of-a-second format (contains X) can be specified.
<li>At most one day format (<var>DD</var> or <var>Day</var>) can be specified. </li>
<li>If DDD is specified, then neither a day nor month format can be.
 
</ul>
<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 ZYY is specified in a format string, no other token that
 
denotes a variable-length value may be used.
<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. </li>
</ul> </li>
 
<li>If <var>ZYY</var> is specified in a format string, no other token that denotes a variable-length value may be used. </li>
 
<li>If a format string contains other tokens that denote variable
<li>If a format string contains other tokens that denote variable
length values, then an * token may only appear as the last character
length values, then an <var>*</var> token may only appear as the last character of the format string. </li>
of the format string.
 
<li>The DAY token may not be immediately followed by another token
<li>The <var>DAY</var> token may not be immediately followed by another token whose value may be numeric, regardless of whether the following
whose value may be numeric, regardless of whether the following
token represents a variable length value.
token repsents 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>,
Thus, DAY may not be followed by *, I, YY, YYYY, CYY, MM, HH, MI, SS,
<var>X</var>, <var>XX</var>, or <var>XXX</var>; <var>DAY</var> may not be followed by a decimal digit separator,
X, XX, or XXX; DAY 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.  
and DAY may not be followed by a quote followed by a decimal digit.
<p class="note"><b>Note:</b>
A common mistake is to use <var>MM</var> for minutes; it should be
<var>MI</var>.</p></li>
 
<li>When a pair of format strings are used for transforming date values,
<li>When a pair of format strings are used for transforming date values,
for example for #DATECNV or processing of updates to SIRFIELD RELATEd fields,
for example for <var>#DATECNV</var> or processing of updates to SIRFIELD RELATEd fields, additional rules apply to the pattern matching tokens:
additional rules apply to the pattern matching tokens:
<ul>
<ul>
<li>If one of the format strings includes one or more "I"
<li>If one of the format strings includes one or more <var>I</var>
tokens, then the other format string must contain the same
tokens, then the other format string must contain the same
number of "I" tokens.
number of <var>I</var> tokens.
Note that the placement of "I" tokens within the format
Note that the placement of <var>I</var> tokens within the format
strings is not restricted.
strings is not restricted.
The "I" tokens are processed left to right, with
The <var>I</var> tokens are processed left to right, with
each character from the input string that corresponds to the
each character from the input string that corresponds to the
nth "I" token in the input format being copied unchanged 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
character position in the output string that corresponds to the
nth "I" token in the output format.
<i>n</i>th <var>I</var> token in the output format. </li>
<li>If one of the format strings contains an asterisk ( * ) token,
 
<li>If one of the format strings contains an asterisk (<var>*</var>) token,
then the other format string must also contain an asterisk token.
then the other format string must also contain an asterisk token.
All of the characters from the input string that correspond to the
All of the characters from the input string that correspond to the
asterisk token in the input format, if any, are copied unaltered
asterisk token in the input format, if any, are copied unaltered
to the output string, begining in the position that corresponds to
to the output string, begining in the position that corresponds to
the asterisk token in the output format.
the asterisk token in the output format. </li>
</ul>
</ul>
<p></p>
<p>
SIRFIELD is part of the <var class="product">Sir2000 Field Migration Facility</var>.
<var>SIRFIELD</var> is part of the <var class="product">Sir2000 Field Migration Facility</var>. </p></li>
<li>The maximum length of a format string
 
is 100 characters.
<li>The maximum length of a format string is 100 characters. </li>
</ol>
</ol>
<p></p>
 
<b>Note:</b>
===Valid tokens===
A common mistake is to use "MM" for minutes; it should be
"MI".
<p></p>
The valid tokens in a date format are shown in the following list.
The valid tokens in a date format are shown in the following list.
In general, the output format rule for a token is shown.
In general, the output format rule for a token is shown.
Line 202: Line 210:
to match a token on input that would not be produced by that token
to match a token on input that would not be produced by that token
on output.
on output.
<p></p>
 
All of the tokens that match alphabetic strings (for example, "MON")
All of the tokens that match alphabetic strings (for example, <var>MON</var>) match any case for non-strict matching.
xatch any case for non-strict matching.
All other tokens that have differing strict and non-strict matching
All other tokens that have differing strict and non-strict matching
rules are listed under "Special date format rules" in
rules have usage notes in [[#fmtex|Datetime and format examples]].
the index at the back of the manual, and usage notes for them are
contained in [[#fmtex|Datetime and format examples]].
Each input datetime format argument in the description of a #function
Each input datetime format argument in the description of a #function
specificies whether the use of the format observes strict or non-strict
specifies whether the use of the format observes strict or non-strict format matching. See [[#strict|Strict and non-strict format matching]].
format matching.
 
See [[#strict|Strict and non-strict format matching]].
<table class="thJustBold">
<p></p>
<tr><th>NM</th>
<table>
<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>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>NS</th>
<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>
<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>*</th><td>Ignore entire variable-length substring matching pattern, if any, when only 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 input value to location identified by * token in output string. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>I</th><td>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 location in the output string identified by the corresping I token in the output format. See [[#fmtex|Datetime and format examples]].</td></tr>
<tr><th>ND</th>
<tr><th>"</th><td>Following character is "quoted", that is, it acts as a separator character. See [[#fmtex|Datetime and format examples]].</td></tr>
<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>YYYY</th><td>Four-digit year</td></tr>
 
<tr><th>YY</th><td>Two-digit year</td></tr>
<tr><th>*</th>
<tr><th>CYY</th><td>Year minus 1900 (three digits, including any leading zero). See [[#fmtex|Datetime and format examples]].</td></tr>
<td>Ignore entire variable-length substring matching pattern, if any, when only 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 input value to location identified by asterisk (<tt>*</tt>) token in output string. See [[#fmtex|Datetime and format examples]].</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 [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>MONTH</th><td>Full-month name (uppercase variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but all uppercase is always produced on output.</td></tr>
<tr><th>I</th>
<tr><th>Month</th><td>Full-month name (mixed-case variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but an initial uppercase letter followed by all lowercase is always produced on output.</td></tr>
<td>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 <code>I</code> token from from the input value to location in the output string identified by the corresponding <code>I</code> token in the output format. See [[#fmtex|Datetime and format examples]].</td></tr>
<tr><th>MON</th><td>Three-character month abbreviation (uppercase). Non-strict #functions allow any mixture of upper 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). Non-strict #functions allow any mixture of upper and lower case on input, but initial upper case letter followed by all lowercase is always produced on output.</td></tr>
<tr><th>"</th>
<tr><th>MM</th><td>Two-digit month number. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See [[#fmtex|Datetime and format examples]].</td></tr>
<td>Following character is "quoted," that is, it acts as a separator character. See [[#fmtex|Datetime and format examples]].</td></tr>
<tr><th>BM</th><td>Two-character month number; 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. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>DDD</th><td>Three-digit Julian day number</td></tr>
<tr><th>YYYY</th>
<tr><th>DD</th><td>Two-digit day number. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See [[#fmtex|Datetime and format examples]].</td></tr>
<td>Four-digit year.</td></tr>
<tr><th>BD</th><td>Two-character day number; 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. See [[#fmtex|Datetime and format examples]].</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. See [[#fmtex|Datetime and format examples]].</td></tr>
<tr><th>YY</th>
<tr><th>WKDAY</th><td>Full day-of-week name (uppercase variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but all uppercase is always produced on output.</td></tr>
<td>Two-digit year.</td></tr>
<tr><th>Wkday</th><td>Full day-of-week name (mixed-case variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but initial upper case letter followed by all lowercase is always produced on output.</td></tr>
 
<tr><th>WKD</th><td>Three-character day-of-week abbreviation (uppercase). Non-strict #functions allow any mixture of uppercase and lowercase on input, but all uppercase is always produced on output.</td></tr>
<tr><th>CYY</th>
<tr><th>Wkd</th><td>Three-character day-of-week abbreviation (mixed case). Non-strict #functions allow any mixture of uppercase and lowercase on input, but initial upper case letter followed by all lowercase is always produced on output.</td></tr>
<td>Year minus 1900 (three digits, including any leading zero). See [[#fmtex|Datetime and format examples]].</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. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>BH</th><td>Two-character hour number; 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. See [[#fmtex|Datetime and format examples]].</td></tr>
<tr><th>ZYY</th>
<tr><th>MI</th><td>Two-digit minute number</td></tr>
<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 [[#fmtex|Datetime and format examples]].</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>MONTH</th>
<tr><th>XX</th><td>Hundredths of a second</td></tr>
<td>Full-month name (uppercase variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but all uppercase is always produced on output.</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>Month</th>
<tr><th>PM</th><td>AM/PM indicator</td></tr>
<td>Full-month name (mixed-case variable length). Non-strict #functions allow any mixture of uppercase and lowercase on input, but an initial uppercase letter followed by all lowercase is always produced on output.</td></tr>
 
<tr><th>MON</th>
<td>Three-character month abbreviation (uppercase). 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). 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. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>BM</th>
<td>Two-character month number; 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. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>DDD</th>
<td>Three-digit Julian day number.</td></tr>
 
<tr><th>DD</th>
<td>Two-digit day number. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>BD</th>
<td>Two-character day number; 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. See [[#fmtex|Datetime and format examples]].</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. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>WKDAY</th>
<td>Full day-of-week name (uppercase variable length). 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). 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). 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). 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. See [[#fmtex|Datetime and format examples]].</td></tr>
 
<tr><th>BH</th>
<td>Two-character hour number; 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. See [[#fmtex|Datetime and format examples]].</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>
</table>
<p></p>
 
===Valid separators===
The valid separators in a date format are:
The valid separators in a date format are:
<p></p>
 
<ul>
<ul>
<li>blank (" ")
<li>blank (" ")
<li>apostrophe ("'")
<li>apostrophe (<tt>'</tt>)
<li>slash ("/")
<li>slash (<tt>/</tt>)
<li>colon (":")
<li>colon (<tt>:</tt>)
<li>hyphen ("-")
<li>hyphen (<tt>-</tt>)
<li>back slash ("\")
<li>back slash (<tt>\</tt>)
<li>period (".")
<li>period (<tt>.</tt>)
<li>comma (",")
<li>comma (<tt>,</tt>)
<li>underscore ("_")
<li>underscore (<tt>_</tt>)
<li>left parenthesis ("(")
<li>left parenthesis (<tt>(</tt>)
<li>right parenthesis (")")
<li>right parenthesis (<tt>)</tt>)
<li>plus ("+")
<li>plus (<tt>+</tt>)
<li>vertical bar ("|")
<li>vertical bar (<tt>|</tt>)
<li>equals ("=")
<li>equals (<tt>=</tt>)
<li>ampersand ("&amp;")
<li>ampersand (<tt>&amp;</tt>)
<li>at sign ("@")
<li>at sign (<tt>@</tt>)
<li>sharp ("#")
<li>sharp (<tt>#</tt>)
<li>the decimal digits ("0" - "9").
<li>the decimal digits (0 - 9)
</ul>
</ul>
In addition, any character may be a separator character if preceeded by
In addition, any character may be a separator character if preceeded by
the quoting character (").
the quoting character (<tt>"</tt>).
<p></p>
 
See [[#fmtex|Datetime and format examples]] for examples which include use of various
See [[#fmtex|Datetime and format examples]] for examples that include the use of various
separator characters.
separator characters.
==Valid datetimes==
==Valid datetimes==
For a datetime string to be valid it must meet the following criteria:
For a datetime string to be valid it must meet the following criteria:
<ul>
<ul>
<li>Its length must be less than 128 characters.
<li>Its length must be less than 128 characters. </li>
<li>It must be compatible with its corresponding format string.
 
<li>It must represent a valid date and/or time.
<li>It must be compatible with its corresponding format string. </li>
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
<li>It must represent a valid date and/or time.  
in leap years (only centuries divisible by 4 are leap years: 2000 is
<p>
but neither 1800, 1900, nor 2100 are).
For example, at most <code>23:59:59.999</code> for a time, <code>01-12</code> for a month, <code>01-31</code> or less (depending on the month) for a day. February 29 is only valid
<blockquote class="note"><b>Note:</b> Weekdays are not checked for consistency against the date;
in leap years (only centuries divisible by 4 are leap years: 2000 is but neither 1800, 1900, nor 2100 are). </p>
for example, both Saturday, 02/15/97 and Friday, 02/15/97 are valid.
<p class="note"><b>Note:</b> Weekdays are not checked for consistency against the date.
</blockquote>
For example, both <code>Saturday, 02/15/97</code> and <code>Friday, 02/15/97</code> are valid.
<li>It must be within the date range allowed for the corresponding
</p> </li>
format.
 
A datetime string used with a CYY or ZYY format can only represent
<li>It must be within the date range allowed for the corresponding format.
<p>
A datetime string used with a <var>CYY</var> or <var>ZYY</var> format can only represent
dates from 1900 to 2899, inclusive.
dates from 1900 to 2899, inclusive.
A datetime string used with a YY format can only represent
A datetime string used with a <var>YY</var> format can only represent
dates in a range of 100 or less years, as determined by CENTSPAN and
dates in a range of 100 or less years, as determined by <var>CENTSPAN</var> and
SPANSIZE.
<var>SPANSIZE</var>.
The valid range of dates for all other formats is from 1 January 1753
The valid range of dates for all other formats is from <code>1 January 1753</code>
thru 31 December 9999.
thru <code>31 December 9999</code>. </p></li>
</ul>
</ul>
==Processing dates with two-digit year values==
==Processing dates with two-digit year values==
<p></p>
A date field with only two digits for its year value is capable of
A date field with only two digits for the year value is capable of
representing a range of up to one hundred years.
representing a range of up to one hundred years.
When we compare a pair of two-digit year values we are accustomed to
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"
thinking of the century as fixed, so all dates are either "19xx"
or "20xx".
or "20xx".
However, a date field with two-digit year values can actually represent
However, a date field with two-digit year values can actually represent
Line 311: Line 383:
of dates does not exceed 100 years.
of dates does not exceed 100 years.
   
   
<div id="cspan"></div>
===<b id="cspan"></b>CENTSPAN===
===CENTSPAN===
<var>CENTSPAN</var> provides a mechanism for unambiguously converting dates with
<!--Caution: <div> above-->
<p></p>
CENTSPAN provides a mechanism for unambiguously converting dates with
two-digit year values into dates with four-digit year values.
two-digit year values into dates with four-digit year values.
The CENTSPAN mechanism allows two-digit year values to span two centuries
The <var>CENTSPAN</var> mechanism allows two-digit year values to span two centuries
without confusion.
without confusion.
CENTSPAN identifies the four-digit year value that is the <i>start</i>
<var>CENTSPAN</var> identifies the four-digit year value that is the <i>start</i>
of a range of years represented by the two-digit year values.
of a range of years represented by the two-digit year values.
<p></p>
 
CENTSPAN may be specified as an <i>absolute</i> unsigned four digit
<var>CENTSPAN</var> may be specified as an <i>absolute</i> unsigned four-digit
value between 1753 and 9999, or it may be specified as a <i>relative</i>
value between 1753 and 9999, or it may be specified as a <i>relative</i>
signed value between -99 and +99, inclusive.
signed value between -99 and +99, inclusive.
A relative CENTSPAN value is dynamically converted to an <i>effective</i>
A relative <var>CENTSPAN</var> value is dynamically converted to an <i>effective</i>
absolute value before it is used to perform a YY to YYYY conversion.
absolute value before it is used to perform a <var>YY</var> to <var>YYYY</var> conversion.
The effective CENTSPAN value is formed by adding the relative CENTSPAN to the
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.
current four-digit year value at the time the relative value is converted.
 
<p class="code"><nowiki> -- get picture centspan --
<p class="figure">[[File:Centspan1.png|532px]] </p>
</nowiki></p>
 
A simple algorithm is used to convert a two-digit year value (YY) to a
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
four-digit year value, using a four-digit absolute or effective
CENTSPAN value (HHLL).
<var>CENTSPAN</var> value (<var>HHLL</var>).
If the two-digit year value is less than the low-order two digits of the
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
<var>CENTSPAN</var> value, then the resulting century is one greater than the
high-order two digits of the CENTSPAN value.
high-order two digits of the <var>CENTSPAN</var> value.
Otherwise the resulting century is the same as the high-order two
Otherwise the resulting century is the same as the high-order two
digits of the CENTSPAN value.
digits of the <var>CENTSPAN</var> value.
<p></p>
 
<p></p>
Using all one hundred available years for mapping two-digit year
Using all one hundred available years for mapping two-digit year
values can cause significant confusion and result in data integrity
values can cause significant confusion and result in data integrity
errors:
errors: dates just above and just below the 100-year window
dates just above and just below the 100-year window
are mapped to the other end of the window.
are mapped to the other end of the window.
From the previous example, the date "47" will be intepreted
From the previous example, the date <code>47</code> will be intepreted
as 1947, when it could have conceivably been 2047.
as 1947, when it could have conceivably been 2047.
Similarly, the date "46" will be intepreted as 2046, when it
Similarly, the date <code>46</code> will be intepreted as 2046, when it
might have been 1946.
might have been 1946.
<p class="code"><nowiki> -- get picture spam1 --
 
</nowiki></p>
<p class="figure">[[File:Centspan2.png|366px]] </p>
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 <var>CENTSPAN</var> is set to a value that is too high, dates that are just
If CENTSPAN is set to a value that is too low, dates that fall just
prior to <var>CENTSPAN</var> will appear to occur 100 years hence.
after CENTSPAN+99 will appear to have occured 100 years earlier.
If <var>CENTSPAN</var> is set to a value that is too low, dates that fall just
A full one-hundred year window also can not detect attempts to represent
after <code>CENTSPAN+99</code> will appear to have occurred 100 years earlier.
A full one-hundred year window also cannot detect attempts to represent
more than one hundred years of values with a two-digit year.
more than one hundred years of values with a two-digit year.
   
   
<div id="ssize"></div>
===<b id="ssize"></b>SPANSIZE===
===SPANSIZE===
<!--Caution: <div> above-->
<p></p>
There is a method to protect from the ambiguities that
There is a method to protect from the ambiguities that
can occur at each end of the 100-year window defined by CENTSPAN.
can occur at each end of the 100-year window defined by <var>CENTSPAN</var>.
SPANSIZE is used to restrict the size of the window used
<var>SPANSIZE</var> is used to restrict the size of the window used
for mapping two-digit year values.
for mapping two-digit year values.
The effect is to create two <i>guard bands</i>, one just below the
The effect is to create two <i>guard bands</i>, one just below the
Line 373: Line 436:
An attempt to represent a date value that lands in a guard band produces
An attempt to represent a date value that lands in a guard band produces
an error.
an error.
<p></p>
 
Each guard band contains CENTSPAN-SPANSIZE years, hence a SPANSIZE of
Each guard band contains <code>CENTSPAN-SPANSIZE</code> years, hence a <var>SPANSIZE</var> of
100 removes the protection.
100 removes the protection.
SPANSIZE is a value which you can customize in your load module;
<var>SPANSIZE</var> is a value that you can customize in your load module;
see [[#dfcs|CENTSPAN and SPANSIZE]].
see [[Fast/Unload customization of defaults#dfcs|CENTSPAN and SPANSIZE]].
If you do not customize it, the value of
If you do not customize it, the value of
SPANSIZE is 90, which provides protection
<var>SPANSIZE</var> is 90, which provides protection
for two ten year windows: one below the CENTSPAN setting and one
for two ten-year windows: one below the <var>CENTSPAN</var> setting and one
starting at CENTSPAN+90.
starting at <code>CENTSPAN+90</code>.
Note that in <var class="product">Fast/Unload</var> version 3.0, SPANSIZE is 100 (and it can not be
Note that in <var class="product">Fast/Unload</var> version 3.0, <var>SPANSIZE</var> is 100 (and it cannot be customized).
customized).
 
<p></p>
From the <var>CENTSPAN</var> example above:
From our previous example:
 
<p class="code"><nowiki> -- get picture spam2 --
<p class="figure">[[File:Spansize.png|388px]] </p>
</nowiki></p>
 
An attempt to represent the values "37" through "46" will
An attempt to represent the values <code>37</code> through <code>46</code> will
be rejected.
be rejected.
This protects the range 1937 through 1946 as well as the range 2037
This protects the range 1937-through-1946 as well as the range 2037-through-2046.
through 2046.
Note that an intended value of 2047, expressed as <code>47</code> will be
Note that an intended value of 2047, expressed as "47" will be
accepted and interpreted as 1947.
accepted and interpreted as 1947.
In general a smaller SPANSIZE provides the highest assurance of correct
In general a smaller <var>SPANSIZE</var> provides the highest assurance of correct
mappings.
mappings.
However, any setting of SPANSIZE less than 100 will probably detect the
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.
case where a range greater than one hundred years is being used.
   
   
<div id="strict"></div>
==<b id="strict"></b>Strict and non-strict format matching==
==Strict and non-strict format matching==
As mentioned in [[#dtfmt|Datetime formats]], for some of the #functions,
<!--Caution: <div> above-->
the input format rule for a token is the same as the output format rule.
This is the definition of "strict date format matching."
<p></p>
As mentioned in [[#dtfmt|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
However, non-strict #functions sometimes allow a string
to match a token on input that would not be produced by that token
to match a token on input that would not be produced by that token on output.
on output.
<p>
The types of strict matching are as follows:
The types of strict matching are as follows:
<table>
</p>
<tr><th>Alpha tokens</th><td>For alphabetic tokens (for example, <b>Month</b>), 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.</td></tr>
{{Template:Datetime format matching}}
<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>
If you want to check a datetime string using strict rules, you can use the following technique with the non-strict date #functions:
<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>
<p class="code">IF <i>date</i> EQ &apos;' OR <i>date</i> NE #NM2DATE(#DATE2NM(<i>date</i>, <i>fmt</i>), <i>fmt</i>) THEN
<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>
<p></p>
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 <i>date</i> EQ &apos;' OR <i>date</i> NE #NM2DATE(-
  #DATE2NM(<i>date</i>, <i>fmt</i>), -
  <i>fmt</i>) THEN
   <i>error handling</i>
   <i>error handling</i>
END IF
END IF
</p>
</p>
 
<div id="fmtex"></div>
==<b id="fmtex"></b>Datetime and format examples==
==Datetime and format examples==
There is an extensive set of format tokens, as shown in [[#dtfmt|Datetime formats]].
<!--Caution: <div> above-->
<p></p>
There is an extensive set of format tokens, as shown in
[[#dtfmt|Datetime formats]].
These tokens and the various separator characters
These tokens and the various separator characters
can be combined in almost limitless possibility, giving
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.
This section provides examples of some common datetime formats, and
This section provides examples of some common datetime formats, and it
also tries to explain the use of some of the format tokens which
also tries to explain the use of some of the format tokens that
might not be obvious.
might not be obvious.
It also has examples for
<p>
formats which have usage with the <var class="product">Fast/Unload</var> which differs from their
This section also has examples for
formats used with <var class="product">Fast/Unload</var> that differ from their
usage with other <var class="product">Model 204</var> products.
usage with other <var class="product">Model 204</var> products.
These are noted in the examples and are indexed at the back of this
These are noted in the examples. </p>
manual under the heading "Special date format rules".
<p>
Each example format is explained and also presented with some matching
Each example format is explained and is also presented with some matching
datetimes; again, bear in mind that these tokens can be combined in
datetimes, demonstrating only a few of the very many ways these tokens can be combined.
very many ways and only a very few are shown here.
It is assumed that these examples are invoked sometime between the
It is assumed that these examples are invoked sometime between the
years 1998-2040, as the basis for relative CENTSPAN calculations.
years 1998-2040, as the basis for relative <var>CENTSPAN</var> calculations.</p>
<table>
 
<tr><th>YYMMDD</th><td>This is the common 6-digit date format which supports sort order if all dates are within a single century. The following FUEL fragment
<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 FUEL fragment prints the value <code>OK</code>:
<p class="code"><nowiki>%X = #DATE2ND('960229', 'YYMMDD')
<p class="code"><nowiki>%X = #DATE2ND('960229', 'YYMMDD')
IF %X > -9.E12 THEN
IF %X > -9.E12 THEN
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the value "OK".</td></tr>
</nowiki></p></td></tr>
<tr><th>YYYYMMDD</th><td>This is the common 8-digit date format which supports sort order with dates in 2 centuries. The following FUEL fragment
 
<tr><th>YYYYMMDD</th>
<td>This is the common eight-digit date format which supports sort order with dates in two centuries. The following FUEL fragment prints the value <code>19921212</code>:
<p class="code"><nowiki>%N = #DATE2ND('921212', 'YYMMDD')
<p class="code"><nowiki>%N = #DATE2ND('921212', 'YYMMDD')
%N = #ND2DATE(%N, 'YYYYMMDD')
%N = #ND2DATE(%N, 'YYYYMMDD')
REPORT %N
REPORT %N
</nowiki></p> prints the value 19921212.</td></tr>
</nowiki></p></td></tr>
<tr><th>MM/DD/YY</th><td>This is the U.S. 6-digit date format for display. The following FUEL fragment
 
<tr><th>MM/DD/YY</th>
<td>This is the U.S. six-digit date format for display. The following FUEL fragment prints the value <code>OK</code>:
<p class="code"><nowiki>%X = #DATE2ND('12/14/94', 'MM/DD/YY')
<p class="code"><nowiki>%X = #DATE2ND('12/14/94', 'MM/DD/YY')
IF %X > -9.E12 THEN
IF %X > -9.E12 THEN
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the value "OK". <p></p> Notes: <ul>
</nowiki></p>
<li>With non-strict format matching, such as #DATE2ND, the leading zero corresponding to an MM token may be given as a blank, thus allowing "&nbsp;7/15/98". With strict matching, however, such leading blank is not allowed for MM; a leading blank month value with a strict #function 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 <nowiki>#function</nowiki>. The BM token is available starting with version 3.2 of <var class="product">Fast/Unload</var>. </ul></td></tr>
<p>Notes: </p>
<tr><th>DD.MM.YY</th><td>This is a European 6-digit date format for display. The following FUEL fragment
<ul>
<li>With non-strict format matching, such as with <var>#DATE2ND</var>, the leading zero corresponding to an <var>MM</var> token may be given as a blank, thus allowing <code>'&nbsp;7/15/98'</code>.  
<p>
With strict matching, however, such a leading blank is not allowed for <var>MM</var>. A leading blank month value with a strict #function requires the <var>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. </p></li>
</ul></td></tr>
 
<tr><th>DD.MM.YY</th>
<td>This is a European six-digit date format for display. The following FUEL fragment prints the value <code>OK</code>:
<p class="code"><nowiki>%X = #DATE2ND('14.12.94', 'DD.MM.YY')
<p class="code"><nowiki>%X = #DATE2ND('14.12.94', 'DD.MM.YY')
IF %X > -9.E12 THEN
IF %X > -9.E12 THEN
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the value "OK". <p></p> Notes: <ul>
</nowiki></p>
<li>With non-strict format matching, such as #DATE2ND, the leading zero corresponding to a DD token may be given as a blank, thus allowing "&nbsp;1.01.00". With strict matching, however, such leading blank is not allowed for DD; a leading blank day value with a strict #function 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 <nowiki>#function</nowiki>. The BD token is available starting with version 3.2 of <var class="product">Fast/Unload</var>. </ul></td></tr>
<p>
<tr><th>Wkday, DAY Month YYYY "A"T HH:MI</th><td>This is a format which could be used for report headers. The following FUEL fragment
Notes: </p>
<ul>
<li>With non-strict format matching, such as with <var>#DATE2ND</var>, the leading zero corresponding to a <var>DD</var> token may be given as a blank, thus allowing <code>'&nbsp;1.01.00'</code>.  
<p>
With strict matching, however, such a leading blank is not allowed for <var>DD</var>. A leading blank day value with a strict #function requires the <var>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</p></li>
</ul></td></tr>
 
<tr><th nowrap>Wkday, DAY Month YYYY "A"T HH:MI</th>
<td>You can use this format for report headers. The following FUEL fragment prints a value like <code>Friday, 7 February 1998 AT 21:33</code>:
<p class="code"><nowiki>%N = #DATE -
<p class="code"><nowiki>%N = #DATE -
   ('Wkday, DAY Month YYYY "A"T HH:MI')
   ('Wkday, DAY Month YYYY "A"T HH:MI')
REPORT %N
REPORT %N
</nowiki></p> prints a value like "Friday, 7 February 1998 AT 21:33". <p></p> Notes: <ul>
</nowiki></p>
<li>If an input format contains AM or PM, then the time (HH:MI) must be between 00:01 and 12:00 and must be accompanied by either AM or PM.
<p>
<li>If an input format contains DAY (e.g., "DAY MON YY") with non-strict format matching, such as #DATE2ND, the string matching it may have a leading zero, thus allowing "06 MAY 98". With strict matching #functions however, such leading zero is not allowed for DAY; a single digit must be supplied for days 1 through 9.
Notes: </p>
<li>If an input format contains HH with non-strict format matching, such as #DATE2ND, the string matching it may have a leading blank, thus allowing "&nbsp;8:30". With strict matching, however, such 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 <nowiki>#function</nowiki>. The BH token is available starting with version 3.2 of <var class="product">Fast/Unload</var>. </ul></td></tr>
<ul>
<tr><th>YYIIII</th><td>This is a format which could be used for data which contains a 2-digit year prefixing other information, such as a sequence number. The following FUEL fragment
<li>If an input format contains <var>AM</var> or <var>PM</var>, 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>
 
<li>If an input format contains <var>DAY</var> (for example, <var>DAY MON YY</var>) with non-strict format matching, such as with <var>#DATE2ND</var>, a matching string may have a leading zero, thus allowing <code>06 MAY 98</code>. With strict matching #functions however, such a leading zero is not allowed for <var>DAY</var>, and a single digit must be supplied for days 1 through 9. </li>
 
<li>If an input format contains <var>HH</var> with non-strict format matching, such as with <var>#DATE2ND</var>, a matching string may have a leading blank, thus allowing <code>'&nbsp;8:30'</code>. With strict matching, however, such a leading blank is not allowed for <var>HH</var>. A leading blank hour value with a strict #function 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. </li>  
</ul></td></tr>
 
<tr><th>YYIIII</th>
<td>This format can be used for data that contains a two-digit year prefixing other information, such as a sequence number. The following FUEL fragment prints the value <code>02</code>:
<p class="code"><nowiki>%D = #DATE2ND('92ABCD', 'YYIIII')
<p class="code"><nowiki>%D = #DATE2ND('92ABCD', 'YYIIII')
%D = %D + 10*365.25 + .8
%D = %D + 10*365.25 + .8
%N = #ND2DATE(%D, 'YY')
%N = #ND2DATE(%D, 'YY')
REPORT %N
REPORT %N
</nowiki></p> prints the value "02". <p></p> Note: <ul>
</nowiki></p>
<li>When a pair of format strings are used for transforming date values, e.g. for #DATECNV or processing of updates to SIRFIELD RELATEd fields, both formats must have the same number of I tokens. <p></p> SIRFIELD is part of the <var class="product">Sir2000 Field Migration Facility</var>. </ul></td></tr>
<p>
<tr><th>YY*</th><td>This is a format which could be used for data which contains a 2-digit year prefixing other information, such as a sequence number, when the other information is variable length. The following FUEL fragment
Note: </p>
<ul>
<li>When a pair of format strings are used for transforming date values, for example, for <var>#DATECNV</var> or processing of updates to fields that are connected by a <var>SIRFIELD RELATE</var> command, both formats must have the same number of <code>I</code> tokens.  
<p>
The <var>SIRFIELD</var> command is part of the <var class="product">[[Media:SirfieldNew.pdf|Sir2000 Field Migration Facility]]</var>. </p></li>
</ul></td></tr>
 
<tr><th>YY*</th>
<td>You can use this format 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 FUEL fragment prints the values <code>OK</code> and <code>OK</code>.
<p class="code"><nowiki>%X = #DATE2ND('92', 'YY*')
<p class="code"><nowiki>%X = #DATE2ND('92', 'YY*')
IF %X > -9.E12 THEN
IF %X > -9.E12 THEN
Line 504: Line 583:
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the values "OK" and "OK". <p></p> Notes: <ul>
</nowiki></p>
<li>At most one occurrence of the * token may appear in a datetime format.
<p>
<li>When a pair of format strings are used for transforming date values, e.g. for #DATECNV or processing of updates to SIRFIELD RELATEd fields, then if a * token appears in one of the formats, a * must also appear in the other format. <p></p> SIRFIELD is part of the <var class="product">Sir2000 Field Migration Facility</var>. </ul></td></tr>
Notes: </p>
<tr><th>CYYDDD</th><td>This is a compact 6-digit date format with explicit century information, from 1900 through and including 2899. The following FUEL fragment
<ul>
<li>At most one occurrence of the asterisk (<tt>*</tt>) token may appear in a datetime format. </li>
 
<li>When a pair of format strings are used for transforming date values, for example for <var>#DATECNV</var> or processing of updates to fields that are connected by a <var>SIRFIELD RELATE</var> command, then if an asterisk token appears in one of the formats, an asterisk must also appear in the other format.  
<p>
The <var>SIRFIELD</var> command is part of the <var class="product">[[Media:SirfieldNew.pdf|Sir2000 Field Migration Facility]]</var>. </p></li>
</ul></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 FUEL fragment prints the value <code>OK</code>:
<p class="code"><nowiki>%X = #DATE2ND('097031', 'CYYDDD')
<p class="code"><nowiki>%X = #DATE2ND('097031', 'CYYDDD')
IF %X > -9.E12 THEN
IF %X > -9.E12 THEN
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the value "OK".</td></tr>
</nowiki></p></td></tr>
<tr><th>ZYYMMDD</th><td>This is a compact 6- or 7-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 1900's. The following FUEL fragment
 
<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 FUEL fragment prints the values <code>OK</code> and <code>OK</code>:
<p class="code"><nowiki>* Check 1 Dec, 1997:
<p class="code"><nowiki>* Check 1 Dec, 1997:
%X = #DATE2ND('971201', 'ZYYMMDD')
%X = #DATE2ND('971201', 'ZYYMMDD')
Line 524: Line 614:
   REPORT 'OK'
   REPORT 'OK'
END IF
END IF
</nowiki></p> prints the values "OK" and "OK". <p></p> Notes: <ul>
</nowiki></p>
<li>With non-strict format matching (such as #DATE2ND), a three digit number with a leading zero may correspond to a ZYY token, thus allowing "0971201". With strict matching, however, a 3 digit value with leading zero is not allowed for ZYY; a 3-digit value less than 100 with a strict #function requires the CYY token. If the data contains values less than 100 as 3 digits in some instances and as 2 digits in others, you must use a non-strict <nowiki>#function</nowiki>. </ul></td></tr>
<p>
<tr><th>YY0000</th><td>Decimal digits can be used as separator characters. The following FUEL fragment
Notes: </p>
<ul>
<li>With non-strict format matching (such as with <var>#DATE2ND</var>), a three-digit number with a leading zero may correspond to a <var>ZYY</var> token, thus allowing <code>0971201</code>.  
<p>
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 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></li>
</ul></td></tr>
 
<tr><th>YY0000</th>
<td>Decimal digits can be used as separator characters. The following FUEL fragment prints the value <code>1992NA</code>:
<p class="code"><nowiki>%N = #DATE2ND('92000', 'YY000')
<p class="code"><nowiki>%N = #DATE2ND('92000', 'YY000')
%N = #ND2DATE(%N, 'YYYY"N"A')
%N = #ND2DATE(%N, 'YYYY"N"A')
REPORT %N
REPORT %N
</nowiki></p> prints the value "1992NA". <p></p> Notes: <ul>
</nowiki></p>
<li>Numeric separators, unlike alphabetic separators, do not need to be preceeded by a quote character (").
<p>
<li>Numeric separators are available starting with version 3.2 of <var class="product">Fast/Unload</var>. </ul></td></tr>
Notes: </p>
<ul>
<li>Numeric separators, unlike alphabetic separators, do not need to be preceeded by a quotation mark character (<tt>"</tt>). </li>
</ul></td></tr>
</table>
</table>
   
   
<div id="dterr"></div>
==<b id="dterr"></b>Datetime error handling==
==Datetime error handling==
<!--Caution: <div> above-->
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</li>
<li>datetime string not matching format
<li>Datetime string not matching format</li>
<li>datetime out of range for the format
<li>Datetime out of range for the format</li>
<li>invalid CENTSPAN value
<li>Invalid <var>CENTSPAN</var> value</li>
<li>datetime out of range for CENTSPAN/SPANSIZE combination
<li>Datetime out of range for <var>CENTSPAN/SPANSIZE</var> combination</li>
</ul>
</ul>
<p></p>
<p>
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: </p>
<ol>
<ul>
<li># Functions using a numeric value to represent a datetime, and
<li>#functions using a numeric value to represent a datetime, and
<nowiki>#TIME</nowiki> and
<var>#TIME</var> and <var>#DATE</var>, have error return values of
<nowiki>#DATE</nowiki>, have error return values of
<code>-9.E12</code> or a null string for numeric or string result #functions, respectively.</li>
-9.E12 or a null string for numeric or string result #functions,
 
respectively.
<li>#functions (other than <var>#TIME</var> and <var>#DATE</var>)
<li># Functions (other than
that only manipulate strings and associated datetime formats
<nowiki>#TIME</nowiki> and
have error return values of a variable number of asterisks (or, in the case of <var>#DATEDIF</var>, the value 99,999,999).</li>
<nowiki>#DATE</nowiki>)
</ul>
that only manipulate
<p>
strings and associated
datetime formats
have error return values of a
variable number of asterisks (or, in the case of #DATEDIF
the value 99,999,999).
</ol>
<p></p>
Most of the standard #DATExxx functions
Most of the standard #DATExxx functions
have an optional output "return code" argument (see
have an optional output "return code" argument (see
[[#funerr|Run-time errors during standard #function calls]]).
[[Fast/Unload standard functions#funerr|Run-time errors during standard #function calls]]).
If you specify, for example,
If you specify, for example, an invalid <var>CENTSPAN</var> argument and you specify the
an invalid CENTSPAN argument and you specify the
return code argument, you can test the return code for <var>CENTSPAN</var> errors. </p>
return code argument, you can test the return code for CENTSPAN
<p>
errors.
If you specify an invalid <var>CENTSPAN</var> argument and you do not specify
If you specify an invalid CENTSPAN argument and you do not specify
the return code argument, the <var class="product">Fast/Unload</var> run terminates with
the return code argument, the <var class="product">Fast/Unload</var> run terminates with
an error message indicating the type of error and the line number
an error message indicating the type of error and the line number
being executed; the argument values are dumped as well.
being executed. In this case, the argument values are dumped as well.</p>
==#DATExxx functions CENTSPAN argument==
 
<p></p>
==CENTSPAN argument of #DATExxx functions==
<p>
Many of the #DATExxx functions accept an optional argument
Many of the #DATExxx functions accept an optional argument
containing a CENTSPAN value to be used for the call.
containing a <var>CENTSPAN</var> value to be used for the call.
The default value of any CENTSPAN argument is -50.
The default value of any <var>CENTSPAN</var> argument is <code>-50</code>.
You can customize the default value of CENTSPAN
You can customize the default value of <var>CENTSPAN</var>
in your load module;
in your load module (see [[Fast/Unload customization of defaults#dfcs|CENTSPAN and SPANSIZE]]).</p>
see [[#dfcs|CENTSPAN and SPANSIZE]].
<p>
Note that in version 3.0 of <var class="product">Fast/Unload</var>, the default CENTSPAN argument
The default value should be adequate in most cases. If you have
can not be customized.
The default value should be adequate in most cases; if you have
carefully determined it should be different in some application,
carefully determined it should be different in some application,
code the value on the relevant #function invocations.
code the value on the relevant #function invocations.</p>
<p></p>
<p>
For a different approach,
For a different approach, see the description of the <var>[[CENTSPLT parameter|CENTSPLT]]</var> and <var>[[DEFCENT parameter|DEFCENT]]</var> parameters and $function arguments.</p>
see the description of the CENTSPLT and DEFCENT
<p class="note"><b>Note:</b>  
parameters (for example, the [[CENTSPLT parameter)
The <var>CENTSPAN</var> argument may not be specified as
and $function arguments.
an entity whose value is <var>MISSING</var>.
<p></p>
For most #function numeric arguments, the <var>MISSING</var> value is allowed if a value of zero for the argument is allowed.
Note that the CENTSPAN argument may not be specified as
Zero is allowed for <var>CENTSPAN</var>, but since it is an unusual
an entity whose value is MISSING.
<var>CENTSPAN</var> value, the <var>MISSING</var> value may <i>not</i> be supplied. </p>
For most #function numeric arguments, the MISSING value is allowed
 
if a value of zero for the argument is allowed.
==Benefits of Fast/Unload datetime processing==
Zero is allowed for CENTSPAN, but since it is an unusual
<p>
CENTSPAN value, the MISSING value may not be supplied.
Following is a list of benefits offered by <var class="product">Fast/Unload</var> datetime processing.
==Benefits of <var class="product">Fast/Unload</var> datetime processing==
To provide concrete comparisons, there are some references to some <var class="product">[[SOUL]]</var> date $functions.</p>
<p></p>
<table class="thJustBold">
Following is a list of benefits offered by <var class="product">Fast/Unload</var>
<tr><th>SPANSIZE</th>
datetime processing.
<td>The <var>SPANSIZE</var> processing creates a very strong barrier to detecting otherwise un-noticed two-digit year processing errors.</td></tr>
To provide concrete comparisons, there
 
are some references to some <var class="product">SOUL</var> date $functions.
<tr><th>Relative <var>CENTSPAN</var></th>
<table>
<td>The relative <var>CENTSPAN</var> specification (for example, <code>-50</code>) allows you to maintain a flexible "rolling" window for two-digit year processing.</td></tr>
<tr><th>SPANSIZE</th><td>The SPANSIZE processing creates a very strong barrier to detecting otherwise un-noticed 2-digit year processing errors.</td></tr>
 
<tr><th>Relative CENTSPAN</th><td>The relative CENTSPAN specification (for example, "-50") allows you to maintain a flexible "rolling" window for 2-digit year processing.</td></tr>
<tr><th>Default CENTSPAN</th>
<tr><th>Default CENTSPAN</th><td>One significant advantage of a relative CENTSPAN is that it allows the default (<b>-50</b>) of a reasonable value without parameter changes in all batch and online jobs.</td></tr>
<td>One significant advantage of a relative <var>CENTSPAN</var> is that it allows the default (<code>-50</code>) 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 <var class="product">Fast/Unload</var> datetime formats. For example, there are 4 different tokens representing the day of the week, and time of day can be represented. Standard User Language date formats do not have any day of week nor time of day tokens, and other standard User Language token variations, for example, CYY vs. ZYY, is done by a complex argument setting.</td></tr>
 
<tr><th>Pattern match tokens</th><td>The <var class="product">Fast/Unload</var> 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 process that year as a date.</td></tr>
<tr><th>Format tokens</th>
<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>
<td>There is a very large set of tokens in the <var class="product">Fast/Unload</var> 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, <var>CYY</var> versus <var>ZYY</var>, are done by a complex argument setting.</td></tr>
<tr><th>Operating on numeric representations</th><td>Numeric date values can be operated on directly with FUEL, especially allowing you to add datetime differences (for example, "+"), rather than calling a DATECHG #function and providing a format.</td></tr>
 
<tr><th>Time</th><td>All <var class="product">Fast/Unload</var> datetime #functions allow any reference to a "date" to include time of day. The only standard User Language datetime $function which provides a time of day is $TIME, the current time of day, in one fixed format.</td></tr>
<tr><th>Pattern match tokens</th>
<tr><th>#DATE formats</th><td>#DATE allows you to specify any format to return the current date and time; $DATE has only a few numeric codes for a few formats.</td></tr>
<td>The <var class="product">Fast/Unload</var> datetime formats can contain single-character (<code>I</code>) or variable-length character (<code>*</code>) 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.</td></tr>
<tr><th>Error control args</th><td><var class="product">Fast/Unload</var> provides error handling control that allows you to identify the specific cause of any datetime error.</td></tr>
 
<tr><th>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>
<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 FUEL, especially allowing you to add datetime differences (for example, <code>+</code>), rather than calling a <var>DATECHG</var> #function and providing a format.</td></tr>
 
<tr><th>Time</th>
<td>All <var class="product">Fast/Unload</var> 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 <var>[[$Time]]</var>, the current time of day, in one fixed format.</td></tr>
 
<tr><th><var>#DATE</var> formats</th>
<td><var>#DATE</var> lets you 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 arguments</th>
<td><var class="product">Fast/Unload</var> provides error handling control that allows you to identify the specific cause of any datetime error.</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: <code>-9.E12</code> or a null string for numeric or string result #functions, respectively.</td></tr>
</table>
</table>
   
   
==See also==
==See also==
[[Fast/Unload overview#WIKFUN$$topics|Fast/Unload topics]]
{{Template:Fast/Unload topic list}}

Latest revision as of 22:48, 8 February 2018

This page presents date processing issues, including usage of Fast/Unload past the year 1999, an explanation of its processing of dates, and any rules and restrictions you must follow to achieve correct results using date values with Fast/Unload.

Overview

Fast/Unload uses dates in the following ways:

  • To examine the CPU clock (as returned by the STCK hardware instruction) to determine the current date, in case Fast/Unload is under a rental or trial agreement
  • As arguments to various #functions, and returned values from them

In addition to the above date processing performed by Fast/Unload, it also unloads Model 204 files and allows manipulation of other values that might contain two-digit year date values. You must ensure that any application using that data has an algorithm or rule for unambiguously determining the correct century for the values.

For example, the UAI statement with the SORT clause allows you to sort by a Model 204 field. If you are sorting by a two-digit year date field, you need to supply information to enable the sort program to determine the century. You can do this using the FORMAT keyword in the UAI SORT items, as described in UNLOAD ALL INFORMATION or UAI.

For headers on pages or rows that occur on printed pages or displayed screens, Rocket Software products generally use a full four-digit year format, although they may display dates with two-digit years in circumstances where the proper century can be inferred from the context.

You must examine all uses of date values in your applications to ensure that each of your applications produces correct results. Furthermore, both the operating system and Model 204 must correctly process and transmit dates beyond 1999 in order for Fast/Unload to operate properly.

Most Fast/Unload date processing involves the use of datetime #functions. Occasionally, these are referred to as the "#DATExxx" functions; this is meant to also include #TIME and the #Nxxx2DATE functions.

In operational terms, there are two classes of datetime #functions:

  • #Functions using a numeric value to represent a datetime, where 0 represents 12:00 AM, 1 January 1900. For example, #DATE2NM and #NM2DATE (number of milliseconds since the start of 1900).

    These #functions perform non-strict matching of date strings to date formats. For example, a leading blank is allowed for the HH token.

  • Other #functions that only manipulate strings and associated datetime formats. For example, #DATECHG (add number of days to given date).

    These #functions perform strict matching of date strings to date formats. For example, a leading blank is not allowed for the HH token. These #functions generally produce the same results as the same SOUL $DATExxx functions, with additional enhancements.

See Strict and non-strict format matching for a discussion of strict and non-strict format matching, including a technique for accomplishing strict date checking using the non-strict #functions.

Notes:

  • All #DATExxx functions that can have argument errors (that is, all #functions except #DATEFMT) accept an optional "return code" argument. If an argument error occurs and the return code argument is absent, Fast/Unload terminates; if the return code argument is present, an error will set the return code to a non-zero number and the result of the #function is the MISSING value.

    The SOUL $DATExxx and $SIR_DATExxx functions take a different approach to error handling. Each uses a special return value (or class of values) to indicate an argument error.

  • The default format for #DATE is YYYY-MM-DD; the default for $Date and $Sir_Date is YY-MM-DD.

The rest of this page contains a discussion of datetime formats, valid datetime strings, processing of two-digit year values, and datetime error handling. It also contains example datetime formats and corresponding example datetime strings. Finally, there is a list of benefits of Fast/Unload datetime processing.

Datetime formats

The representation of a date is determined 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 several Rocket Model 204 add-on products in addition to Fast/Unload. The additional products using datetime format strings are:

The rules for these datetime format strings are consistent throughout all these products, though certain uses of these strings might impose extra restrictions. For example, a leading blank is allowed for the HH, DD, and MM parts of a date argument using a non-strict date #function, such as #DATE2NS, but is not allowed for the strict date #functions.

Basic rules

There are certain rules 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), the format string must consist of only one token. Numeric datetime tokens are only supported in format strings for the Sir2000 Field Migration Facility.
  2. You must specify at least one time, weekday, or date token.
  3. Except for "weekday", you can't 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.
  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.

    Note: A common mistake is to use MM for minutes; it should be MI.

  7. When a pair of format strings are used for transforming date values, for example for #DATECNV or processing of updates to SIRFIELD RELATEd fields, 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 asterisk (*) token, then the other format string must also contain an asterisk token. All of the characters from the input string that correspond to the asterisk token in the input format, if any, are copied unaltered to the output string, begining in the position that corresponds to the asterisk token in the output format.

    SIRFIELD is part of the Sir2000 Field Migration Facility.

  8. The maximum length of a format string is 100 characters.

Valid 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. 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. All other tokens that have differing strict and non-strict matching rules have usage notes 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. See Strict and 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, when only 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 input value to location identified by asterisk (*) token in output string. See Datetime and format examples.
I 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 location in the output string identified by the corresponding I token in the output format. See Datetime and format examples.
" Following character is "quoted," that is, it acts as a separator character. See Datetime and format examples.
YYYY Four-digit year.
YY Two-digit year.
CYY Year minus 1900 (three digits, including any leading zero). See Datetime and format examples.
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). 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). Non-strict #functions allow any mixture of uppercase and lowercase on input, but an initial uppercase letter followed by all lowercase is always produced on output.
MON Three-character month abbreviation (uppercase). 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). 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. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See Datetime and format examples.
BM Two-character month number; 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. See Datetime and format examples.
DDD Three-digit Julian day number.
DD Two-digit day number. Non-strict #functions allow a two-character number with leading blank on input, but two decimal digits are always produced on output. See Datetime and format examples.
BD Two-character day number; 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. See Datetime and format examples.
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. See Datetime and format examples.
WKDAY Full day-of-week name (uppercase variable length). 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). 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). 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). 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. See Datetime and format examples.
BH Two-character hour number; 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. See Datetime and format examples.
MI Two-digit minute number.
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.

Valid 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 (|)
  • equals (=)
  • ampersand (&)
  • at sign (@)
  • sharp (#)
  • the decimal digits (0 - 9)

In addition, any character may be a separator character if preceeded 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. 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 its 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 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.

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: 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 intepreted as 1947, when it could have conceivably been 2047. Similarly, the date 46 will be intepreted 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 cannot detect attempts to represent more than one hundred years of values with a two-digit year.

SPANSIZE

There is a method 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. SPANSIZE is a value that you can customize in your load module; see CENTSPAN and SPANSIZE. 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. Note that in Fast/Unload version 3.0, SPANSIZE is 100 (and it cannot be customized).

From the CENTSPAN example above:

An attempt to represent the values 37 through 46 will be 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 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.

If you want to check a datetime string using strict rules, you can use the following technique with the non-strict date #functions:

IF date EQ '' OR date NE #NM2DATE(#DATE2NM(date, fmt), fmt) THEN error handling END IF

Datetime and format examples

There is an extensive set of format tokens, as shown in Datetime formats. 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 it also tries to explain the use of some of the format tokens that might not be obvious.

This section also has examples for formats used with Fast/Unload that differ from their usage with other Model 204 products. These are noted in the examples.

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

YYMMDD This is the common six-digit date format which supports sort order if all dates are within a single century. The following FUEL fragment prints the value OK:

%X = #DATE2ND('960229', 'YYMMDD') IF %X > -9.E12 THEN REPORT 'OK' END IF

YYYYMMDD This is the common eight-digit date format which supports sort order with dates in two centuries. The following FUEL fragment prints the value 19921212:

%N = #DATE2ND('921212', 'YYMMDD') %N = #ND2DATE(%N, 'YYYYMMDD') REPORT %N

MM/DD/YY This is the U.S. six-digit date format for display. The following FUEL fragment prints the value OK:

%X = #DATE2ND('12/14/94', 'MM/DD/YY') IF %X > -9.E12 THEN REPORT 'OK' END IF

Notes:

  • With non-strict format matching, such as with #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 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 following FUEL fragment prints the value OK:

%X = #DATE2ND('14.12.94', 'DD.MM.YY') IF %X > -9.E12 THEN REPORT 'OK' END IF

Notes:

  • With non-strict format matching, such as with #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 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 You can use this format for report headers. The following FUEL fragment prints a value like Friday, 7 February 1998 AT 21:33:

%N = #DATE - ('Wkday, DAY Month YYYY "A"T HH:MI') REPORT %N

Notes:

  • 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 with #DATE2ND, a matching string may have a leading zero, thus allowing 06 MAY 98. With strict matching #functions however, such a leading zero is not allowed for DAY, and a single digit must be supplied for days 1 through 9.
  • If an input format contains HH with non-strict format matching, such as with #DATE2ND, a matching string 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 can be used for data that contains a two-digit year prefixing other information, such as a sequence number. The following FUEL fragment prints the value 02:

%D = #DATE2ND('92ABCD', 'YYIIII') %D = %D + 10*365.25 + .8 %N = #ND2DATE(%D, 'YY') REPORT %N

Note:

  • When a pair of format strings are used for transforming date values, for example, for #DATECNV or processing of updates to fields that are connected by a SIRFIELD RELATE command, both formats must have the same number of I tokens.

    The SIRFIELD command is part of the Sir2000 Field Migration Facility.

YY* You can use this format 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 FUEL fragment prints the values OK and OK.

%X = #DATE2ND('92', 'YY*') IF %X > -9.E12 THEN REPORT 'OK' END IF %X = #DATE2ND('1992ABC', 'YYYY*') IF %X > -9.E12 THEN REPORT 'OK' END IF

Notes:

  • At most one occurrence of the asterisk (*) token may appear in a datetime format.
  • When a pair of format strings are used for transforming date values, for example for #DATECNV or processing of updates to fields that are connected by a SIRFIELD RELATE command, then if an asterisk token appears in one of the formats, an asterisk must also appear in the other format.

    The SIRFIELD command 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 following FUEL fragment prints the value OK:

%X = #DATE2ND('097031', 'CYYDDD') IF %X > -9.E12 THEN REPORT 'OK' END IF

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 following FUEL fragment prints the values OK and OK:

* Check 1 Dec, 1997: %X = #DATE2ND('971201', 'ZYYMMDD') IF %X > -9.E12 THEN REPORT 'OK' END IF * Check 1 Dec, 2000: %X = #DATE2ND('1001201', 'ZYYMMDD') IF %X > -9.E12 THEN REPORT 'OK' END IF

Notes:

  • With non-strict format matching (such as with #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 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 following FUEL fragment prints the value 1992NA:

%N = #DATE2ND('92000', 'YY000') %N = #ND2DATE(%N, 'YYYY"N"A') REPORT %N

Notes:

  • Numeric separators, unlike alphabetic separators, do not need to be preceeded by a quotation mark character (").

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:

  • #functions using a numeric value to represent a datetime, and #TIME and #DATE, have error return values of -9.E12 or a null string for numeric or string result #functions, respectively.
  • #functions (other than #TIME and #DATE) that only manipulate strings and associated datetime formats have error return values of a variable number of asterisks (or, in the case of #DATEDIF, the value 99,999,999).

Most of the standard #DATExxx functions have an optional output "return code" argument (see Run-time errors during standard #function calls). If you specify, for example, an invalid CENTSPAN argument and you specify the return code argument, you can test the return code for CENTSPAN errors.

If you specify an invalid CENTSPAN argument and you do not specify the return code argument, the Fast/Unload run terminates with an error message indicating the type of error and the line number being executed. In this case, the argument values are dumped as well.

CENTSPAN argument of #DATExxx functions

Many of the #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. You can customize the default value of CENTSPAN in your load module (see CENTSPAN and SPANSIZE).

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, see the description of the CENTSPLT and DEFCENT parameters and $function arguments.

Note: The CENTSPAN argument may not be specified as an entity whose value is MISSING. For most #function numeric arguments, the MISSING value is allowed if a value of zero for the argument is allowed. Zero is allowed for CENTSPAN, but since it is an unusual CENTSPAN value, the MISSING value may not be supplied.

Benefits of Fast/Unload datetime processing

Following is a list of benefits offered by Fast/Unload datetime processing. To provide concrete comparisons, there are some references to some SOUL date $functions.

SPANSIZE The SPANSIZE processing creates a very strong barrier to detecting otherwise un-noticed two-digit year processing errors.
Relative CENTSPAN The relative CENTSPAN specification (for example, -50) allows you to 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 (-50) 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 Fast/Unload 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 Fast/Unload 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 FUEL, especially allowing you to add datetime differences (for example, +), rather than calling a DATECHG #function and providing a format.
Time All Fast/Unload 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.
#DATE formats #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 Fast/Unload provides error handling control that allows you to identify the specific cause of any datetime error.
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.

See also