Date Construct

Combine one or more Enterprise Database Server items using a specified format to create a virtual SQL column of type Date. A name is required for each date column defined as type Date. This name is used in a query specification to access the information as a date. A date can be used anywhere a column can be used.

Single-Field Date

The single-field date is based on one Enterprise Database Server item that is treated as an SQL-92 date. This item is specified according to one of the following formats:

  • YYMMDD

  • MMDDYY

  • DDMMYY

  • YYDDD

  • DDDYY

  • YYYYMMDD

  • MMDDYYYY

  • DDMMYYYY

  • YYYYDDD

  • DDDYYYY

  • DDDDDD

If the Enterprise Database Server item is of type ALPHA or NUMBER, its length must match the width of the chosen date format. If the type is REAL or FIELD, the item is assumed to contain an n-digit integer where n is the length of the chosen date format. For type FIELD, the length must be long enough to handle the chosen date format.

Multi-field Date

The multi-field date is based on two or three Enterprise Database Server items. The two-item date represents the year and a 3-digit day. The three-item date represents the year, month, and day. The items must be of type ALPHA or NUMBER. The year item must have a length of 2 or 4. The month item must have a length of 2. The day item must have a length of 2 if a month item is specified or a length of 3 if a month item is not specified.

Determining the Century

You can define the century for each 2-digit year mapping. You specify a base year that indicates which years belong to which century for a particular date. For example,

  • If the base year is 57, then 57 through 99 are interpreted as 1957 through 1999 and 00 through 56 are interpreted as 2000 through 2056.

  • If the base year is 1900, then 00 through 99 are interpreted as 1900 through 1999.

  • If the base year is 2129, then 29 through 99 are interpreted as 2129 through 2199 and 0 through 28 are interpreted as 2200 through 2228.

  • If a base year is not specified, 1900 is the default base year.

 Caution

When information is stored into a date that is based on a 2-digit year, the century information is not stored. It is derived using the base year when the date is retrieved. This might cause a date stored as 1999-10-11 to be retrieved as 2099-10-11 depending on the specified base year.

User Access

All items mapped to a date have an access type of read-only. This read-only access prevents users from updating both the virtual date value as well as the underlying items. To prevent viewing the underlying items as columns, mark the items using the Ignore option.

Primary Key

An item mapped as a date cannot be used in the specification of relationships or in the primary key. A key with an item mapped to a date is not chosen as the default primary key.

Prev  Up  Next
SQL DATE  Home  Security