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 descriptorMI
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 descriptorMI
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). 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 to19
when year is greater than70
, otherwise20
. -
The system calculates the century automatically. If the year is less than
50
, the system calculates a century of20
; otherwise, the century calculates to19
.
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),