@DATE returns dates and times in a variety of formats to the target column based on the format passed into the source. @DATE converts virtually any type of input into a valid SQL date. @DATE can also be used to extract portions of a date field, or to compute a numeric timestamp field based on a date.

Syntax

@DATE ("output_descriptor", "input_descriptor", source_field
[, "input_descriptor", source_field] [, ...])

output_descriptor

A string containing date descriptors and optional literal values. For example, the descriptor YYYY corresponds to a four-digit year, the descriptor MI describes minutes, while spaces, colons or other literals are output as is. See "Date Descriptors" for descriptions.

input_descriptor

A string containing a series of date descriptors and optional literal values. For example, the descriptor YYYY corresponds to a four-digit year, the descriptor MI describes minutes. Date descriptors are strung together to describe the field or column that follows in the next parameter. See "Date Descriptors" for descriptions.

source_field

The name of a source field supplying the preceding input.

Date Descriptors

date = @date ("JTSLCT", "TTS", @numbin(date));

date = @date ("YYY-MM-DD HH:MI:SS", "JTSLCT", date);

Using JUL and JTS

JUL and JTS produce numbers you can use in numeric expressions.

Example

The following expression produces the time at which an order is filled.

ORDER_FILLED = @DATE ("YYYY-MM-DD:HH:MI:SS", "JTS", @DATE 
("JTS", "YYMMDDHHMISS", ORDER-TAKEN-TIME) + ORDER-MINUTES * 60 * 1000000)

The above expression changes ORDER-TAKEN-TIME into a Julian timestamp, then adds ORDER-MINUTES converted into microseconds to this timestamp (the inner @DATE expression). This expression is passed back as a new Julian timestamp to the outer @DATE expression, which converts it back to a more readable date and time.

Working with Date Strings

Descriptor string "YYYYMMDD" indicates that the following numeric or character field contains (in order) a four-digit year (YYYY), month (MM), and day (DD).

Descriptor string "DD/MM/YY" indicates that the field contains the day, a slash, the month, a slash, and the two digit year.

Converting Two-digit Years into Four-digit Values

In an instance where a two-digit year is supplied, but a four-digit year is required in the output, several options exist.

• A century is hard-coded, as in the "CC", 19 or "CC", 20.

• The @IF function is used, as in "CC", @IF (YY > 70, 19, 20). This causes century to be set to 19 when year is greater than 70, otherwise 20.

• The system calculates the century automatically. If the year is less than 50, the system calculates a century of 20; otherwise, the century calculates to 19.

Example

The following show some ways to use date conversions.

• Converting year, month and day fields into a SQL date.

  DATE_COL = @DATE ("YYYY-MM-DD", "YY", date1.yr, "MM", date1.mm, "DD", date1.dd)

• Converting the date at the group level (assuming year, month, and day are part of date1).

  DATE_COL = @DATE ("YYYY-MM-DD", "YYMMDD", date1)

• Converting to a date and time, defaulting seconds to zero.

  DATE_COL = @DATE ("YYYY-MM-DD:HH:MI:00", "YYMMDD", date1, "HHMI", time1)

• Converting a numeric field stored as YYYYMMDDHHMISS to a SQL date.

  DATETIME_COL = @DATE ("YYYY-MM-DD:HH:MI:SS", "YYYYMMDDHHMISS", NUMERIC-DATE)

• Converting a numeric field stored as YYYYMMDDHHMISS to a Julian timestamp.

  JULIAN_TS_COL = @DATE ("JTS", "YYYYMMDDHHMISS", NUMERIC-DATE)

• Converting a Julian timestamp field to two columns: a datetime field in the format YYYYMMDDHHMISS, and a fraction field, which holds the microseconds portion of the timestamp.

  DATETIME_COL = @DATE ("YYYY-MM-DD:HH:MI:SS", "JTS", JTS-FIELD),
  FRACTION_COL = @DATE ("FFFFFF", "JTS", JTS-FIELD)

Example: Working with an OpenSys timestamps having fractional positions greater than 6 or with time zone.

This example shows how to map a DB2 TIMESTAMP (12) with TIME ZONE to DATETIME fields.

Target columns
DATE12 DATETIME YEAR TO FRACTION(6) DEFAULT NULL, 
DATE12_F DATETIME FRACTION TO FRACTION(6) DEFAULT NULL,
DATE12_TZ INTERVAL HOUR(2) TO MINUTE DEFAULT NULL

date12 = @date ("YYYY-MM-DD:HH:MI:SS.FFFFFF",
                "YYYY-MMDD:HH:MI:SS.FFFFFFNNNPPP", date12),

date12_f = @date ("NNNPPP", "YYYY-MM-DD:HH:MI:SS.FFFFFFNNNPPP", date12),
date12_tz = @date ("TZ", "YYYY-MM-DD:HH:MI:SS.FFFFFFNNNPPP TZ", date12),