@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 |
DD |
Numeric day of month |
DDD |
Numeric day of the year, such as |
DOW0 |
Numeric day of the week where Sunday = |
DOW1 |
Numeric day of the week where Sunday = |
DOWA |
Alphanumeric day of the week, such as |
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 |
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) |
STRATUS |
STRATUS application timestamp that returns microseconds since 1/1/1980. |
CDATE |
C timestamp in seconds since the Epoch. |
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)