Special L1 Conversion of 7-digit yyyyddd Date Values using RSMSXDT2

This section describes the special L1 conversion of possible incomplete 7-digit yyyyddd date values for Lantmäteriet (LM).

See Conversion of 7-digit yyyyddd Date Values using RSMSXDT2 for a description of the standard conversion of 7-digit yyyyddd date values.

 

The RSMSXDT2 exit routine can perform special conversion of possible incomplete 7-digit yyyyddd date values in the DB data format to or from 10-byte 'yyyy-mm-dd' character values in the SP data format. This is indicated by specifying 'L1' as special customer conversion code in positions 5-6 of the EXITPARM operand value in the COLUMN or DOMAIN statement.

The special L1 conversion performed by the RSMSXDT2 exit routine works in the same way as the conversion made by the C2AX270 field exit routine at Lantmäteriet.

The internal field value in the DB data format is a 7-digit yyyyddd date value where yyyy is the year number and ddd is the number of the day in the year. Incomplete dates are indicated by special ddd values as described in the following table:
ddd Value Description and Notes
= 000 No date value is present when yyyy is 0000.
Otherwise, the internal date value contains a year number only and corresponds to a character value of 'yyyy-00-00' in the SP data format. Note that the preferred internal value for a date with year only is yyyy600, but yyyy000 is also accepted during conversion from the DB data format to the SP data format.
 
>= 001 and
<= 365 | 366
The internal date value is a complete date that will be converted to a character value of 'yyyy-mm-dd' in the SP data format and vice versa.
 
= 600 The internal date value contains a year number only and corresponds to a character value of 'yyyy-00-00' in the SP data format. Note that yyyy600 is the preferred internal value for a date with year only. See also ddd = 000 above.
Character value 'yyyy-00-00' in the SP data format is converted to yyyy600 as value in the internal DB data format.
 
> 600 The internal date value contains a year number and a month number but no day number. ddd - 600 gives the day number in the year for the first day of the month.
The internal incomplete date value in the DB data format is converted to character value 'yyyy-mm-00' in the SP data format and vice versa.
Note that any day in a month is accepted during conversion to the SP data format, but when converting a 'yyyy-mm-00' value to internal value in the DB data format, ddd is always set to the first day in the month + 600.
 

The following table shows the COLUMN or DOMAIN statement operands that must be specified for a 7-digit yyyyddd date field with special L1 conversion of incomplete date values. Considerations for some other optional operands are also present. See the Metadata Definitions Guide and Reference book for a full description of the following and other statement operands.

Operands Notes and Considerations
SPTYPE=CHAR and SPLENGTH=10 has to be specified, because the value in the SP data format is an 10-byte character string with 'yyyy-mm-dd' as format.
 
DBTYPE=P and DBFIELDLEN=4 defines a packed decimal 7-digit yyyyddd date field in the DB data format.
DBTYPE=Z and DBFIELDLEN=7 defines a zoned decimal 7-digit yyyyddd date field in the DB data format.
Either DBTYPE=P with DBFIELDLEN=4 or DBTYPE=Z with DBFIELDLEN=7 must be specified.
 
ZONEPOS=C indicates that DB field values should have positive sign, i.e. have x'C' as zone value.
This is the default when DBTYPE=P.
ZONEPOS=F indicates that DB field values should be unsigned, i.e. have x'F' as zone value.
This is the default when DBTYPE=Z.
 
EXITSP=RSMSXDT2 indicates that the RSMSXDT2 data conversion exit routine should be used.
 
EXITPARM=400XL1 requests special L1 conversion of possible incomplete date values at Lantmäteriet.
 
See EXITPARM Option Values to RSMSXDT2 for a complete description of the different conversion options.
 
Note that another low-value option code than X can be specified when desired in the fourth position of EXITPARM operand value.
 
NULLABLE=TRUE can be used when a zero value in the DB data format should be converted to or from NULL as value in the SP data format.
Otherwise, a DB field value of zero will be converted to or from '0000-00-00' as value in the SP data format.
See also the LowValue option in EXITPARM Option Values to RSMSXDT2 for more information.
 
COLLATION=SPONLY must be specified, because the collation sequences of date values differ in the DB and SP data formats. Otherwise, invalid rows may be affected by an SQL statement when a condition with greater or less than is present for the column in the WHERE clause.
 
LATETEST=TRUE

should be specified when a certain value in the SP data format can correspond to more than one value in the DB data format. This will be the case:

  • If internal yyyyddd date values having ddd = 000 may be present, because yyyy000 is converted to 'yyyy-00-00' in the SP data format, but that value will be converted to yyyy600 in the opposite direction.
  • If internal yyyyddd date values having ddd > 600 is present with ddd - 600 not being the first day of a month.
  • If A is specified as low-value option code in the fourth position of the EXITPARM value.

If LATETEST=TRUE is not specified in these cases, too few rows may be returned when a condition for the column is present in the WHERE clause of an SQL statement.
 

 

Example

The following example defines a signed packed decimal yyyyddd field for incomplete date values using special L1 conversion at Lantmäteriet. In addition, a zero value in the DB data format should be converted to or from NULL as value in the SP data format, and conditions must be evaluated in the SP data format (LateTest=True), because some value in the SP data format may correspond to more than one value in the DB data format.

   Column Name=PDate
          SPType=Char      SPLength=10
          DBType=P         DBFieldLen=4  StartOffs=13
          ExitSP=RSMSXDT2  ExitParm=400XL1
          Nullable=True
          Collation=SPOnly
          LateTest=True
Remarks

See Also