4.8 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

CC

YY

YYYY

MM

MMM

DD

DDD

DOW0

DOW1

DOWA

HH

MI

SS

JTSLCT

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

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

JTSGMT

JTS

JUL

TTS

PHAMIS

FFFFFF

STRATUS

CDATE

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)