2.13 @DATE
Use the @DATE
function to return dates and times in a variety of formats to the target column based on the format passed into the source column. @DATE
converts virtually any type of input into a valid SQL date. @DATE
also can be used to extract portions of a date column or to compute a numeric timestamp column based on a date.
Syntax
@DATE ('output_descriptor', 'input_descriptor', source_column [, 'input_descriptor', source_column] [, ...])
-
'output_descriptor'
-
The output of the function. The valid value is a string that is composed of date descriptors and optional literal values, such as spaces or colons, that are required by the target column. Date descriptors can be strung together as needed. See Table 2-1 for descriptions of date descriptors. The format descriptor must match the
date/time/timestamp
format for the target. Oracle GoldenGate overrides the specified format to make it correct, if necessary. -
'input_descriptor'
-
The source input. The valid value is a string that is composed of date descriptors and optional literal values, such as spaces or colons. Date descriptors can be strung together as needed. The following are examples:
-
Descriptor string
'YYYYMMDD
' indicates that the source column specified withsource_column
contains (in order) a four-digit year (YYYY
), month (MM
), and day (DD
). -
Descriptor string
'DD/MM/YY
' indicates that the source column specified withsource_column
contains the day, a slash, the month, a slash, and the two digit year.
See Table 2-1 for date descriptions.
-
-
source_column
-
The name of the numeric or character source column that supplies the input specified with
input_descriptor
.
Table 2-1 Date Descriptors
Descriptor | Description | Valid for... |
---|---|---|
CC |
Century |
Input/Output |
YY |
Two-digit year |
Input/Output |
YYYY |
Four-digit year |
Input/Output |
MM |
Numeric month |
Input/Output |
MMM |
Alphanumeric month, such as |
Input/Output |
DD |
Numeric day of month |
Input/Output |
DDD |
Numeric day of the year, such as |
Input/Output |
DOW0 |
Numeric day of the week (Sunday = |
Input/Output |
DOW1 |
Numeric day of the week (Sunday = |
Input/Output |
DOWA |
Alphanumeric day of the week, such as |
Input/Output |
HH |
Hour |
Input/Output |
MI |
Minute |
Input/Output |
SS |
Seconds |
Input/Output |
JTSLCT |
Use for a Julian timestamp that is already local time, or to keep local time when converting to a Julian timestamp. |
Input/Output |
JTSGMT |
Julian timestamp, the same as |
Input/Output |
JTS |
Julian timestamp. |
Input/Output |
JUL |
Julian day. |
Input/Output |
TTS |
NonStop 48-bit timestamp |
Input |
PHAMIS |
|
Input |
FFFFFF |
Fraction (up to microseconds) |
Input/Output |
STRATUS |
|
Input/Output |
CDATE |
C timestamp in seconds since the Epoch |
Input/Output |
Examples
- Example 1
-
In an instance where a two-digit year is supplied, but a four-digit year is required in the output, several options exist to obtain the correct century.
-
The century can be hard coded, as in:
'CC', 19 or 'CC', 20
-
The
@IF
function can be used to set a condition, as in:'CC', @IF (YY > 70, 19, 20)
This causes the century to be set to 19 when the year is greater than 70; otherwise the century is set to 20.
-
The system can calculate the century automatically. If the year is less than 50, the system calculates a century of 20; otherwise, a century of 19 is calculated.
-
- Example 2
-
The following converts year, month and day columns into a date.
date_col = @DATE ('YYYY-MM-DD', 'YY', date1_yy, 'MM', date1_mm, 'DD', date1_dd)
- Example 3
-
The following converts a date and time, defaulting seconds to zero.
date_col = @DATE ('YYYY-MM-DD HH:MI:00', 'YYMMDD', date1, 'HHMI', time1)
- Example 4
-
The following converts a numeric column stored as
YYYYMMDDHHMISS
to a SQL date.datetime_col = @DATE ('YYYY-MM-DD HH:MI:SS', 'YYYYMMDDHHMISS', numeric_date)
- Example 5
-
The following converts a numeric column stored as
YYYYMMDDHHMISS
to a Julian timestamp.julian_ts_col = @DATE ('JTS', 'YYYYMMDDHHMISS', numeric_date)
- Example 6
-
The following converts a Julian timestamp column to two separate columns: a datetime column in the format
YYYY-MM-DD HH:MI:SS
and a fraction column that 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 7
-
The following produces the time at which an order is filled. The inner
@DATE
expression changes theorder_taken
column into a Julian timestamp, then adds theorder_minutes
column converted into microseconds to this timestamp. The 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.order_filled = @DATE ('YYYY-MM-DD HH:MI:SS', 'JTS', @DATE ('JTS', 'YYMMDDHHMISS', order_taken) + order_minutes * 60 * 1000000)
- Example 8
-
The following does a full calculation of times. It goes from a source date column named
dt
to a target column nameddt5
that is to be converted to the date + 5 hours. The calculation also goes from a source timestamp column namedts
to a target column namedts5
that is to be converted to the timestamp + 5 hours.MAP scratch.t4, TARGET scratch.t4_copy, COLMAP ( USEDEFAULTS, dt5 = @DATE ('YYYY-MM-DD HH:MI:SS', 'JTS', @COMPUTE (@DATE ('JTS', 'YYYY-MM-DD HH:MI:SS', dt) + 18000000000 ) ), ts5 = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF', 'JTS', @COMPUTE ( @DATE ('JTS', 'YYYY-MM-DD HH:MI:SS.FFFFFF', ts) + 18000000000 ) ) ) ;
Parent topic: Column Conversion Functions