DATE

@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

Descriptor	Description
CC	Century
YY	Two-digit year
YYYY	Four-digit year
MM	Numeric month
MMM	Alphanumeric month, such as APR, OCT
DD	Numeric day of month
DDD	Numeric day of the year, such as 001, or 365
DOW0	Numeric day of the week where Sunday = 0.
DOW1	Numeric day of the week where Sunday = 1.
DOWA	Alphanumeric day of the week, such as SUN, MON, TUE
HH	Hour
MI	Minute
SS	Seconds
JTSLCT	Use for a Julian timestamp that is already local time, or to keep local time when converting to a Julian timestamp. An example of a 48-bit NonStop to 64-bit Julian for LCT to LCT time is: date = @date ("JTSLCT", "TTS", @numbin(date)); An example of a NonStop 64-bit Julian for LCT to date type in Oracle: date = @date ("YYY-MM-DD HH:MI:SS", "JTSLCT", date);
JTSGMT	Julian timestamp, the same as JTS
JTS	Julian timestamp. For more information see “Using JUL and JTS”.
JUL	Julian day
TTS	NonStop 48-bit timestamp
PHAMIS	PHAMIS application date format
FFFFFF	Fraction (up to microseconds). This is valid for both source as well as target details.
FFFFFFFFFFFF	Fraction (up to picoseconds) valid for source detail only.
NNN	Fraction for nanoseconds positions.
PPP	Fraction for picoseconds positions.
STRATUS	STRATUS application timestamp that returns microseconds since 1/1/1980.
CDATE	C timestamp in seconds since the Epoch.
TZ	Timezone

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),