@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 can also be used to extract portions of a date column or to compute a numeric timestamp column based on a date. The function

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 3-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 with source_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 with source_column contains the day, a slash, the month, a slash, and the two digit year.

See Table 3-1 for date descriptions.

source_column

The name of the numeric or character source column that supplies the input specified with input_descriptor.

Table 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 `APR`, `OCT`	Input/Output
DD	Numeric day of month	Input/Output
DDD	Numeric day of the year, such as `001` or `365`	Input/Output
DOW0	Numeric day of the week (Sunday = `0`)	Input/Output
DOW1	Numeric day of the week (Sunday = `1`)	Input/Output
DOWA	Alphanumeric day of the week, such as `SUN`, `MON`, `TUE`	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 `JTS.`	Input/Output
JTS	Julian timestamp. `JUL` and `JTS` produce numbers you can use in numeric expressions. The unit is microseconds. On a Windows machine, the value will be padded with zeros (0) because the granularity of the Windows timestamp is milliseconds.	Input/Output
JUL	Julian day. `JUL` and `JTS` produce numbers you can use in numeric expressions.	Input/Output
TTS	NonStop 48-bit timestamp	Input
PHAMIS	`PHAMIS` application date format	Input
FFFFFF	Fraction (up to microseconds)	Input/Output
STRATUS	`STRATUS` application timestamp	Input/Output
CDATE	C timestamp in seconds since the Epoch	Input/Output

ISO 8601 Date-Time Format Support

Oracle GoldenGate supports ISO 8601, which is an internationally recognized format to represent date, time and timezone. Following are the input/output descriptors for this ISO 8601 format:

Table 2 Date Descriptors

Descriptor	Description	Valid for…
ISO8601_DATETIME	ISO 8601 date-time format with optional time zone. Equivalent to `YYYY-MM-DDTHH:MI:SSTZ`.	Input/Output
ISO8601_DATETIME_FRACT	ISO 8601 date-time format with optional fractional seconds and optional time zone. Equivalent to `YYYY-MM-DDTHH:MI:SS.FFFFFFTZ`.	Input/Output
TIMEZN	The time zone specification accepts `+/-HH:MI` or region ID such as `America/New_York`.	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 converts a numeric column stored as YYYYMMDDHHMISS to an ISO 8601 format.

iso_datetime_col = @DATE ('YYYY-MM-DDTHH:MI:SSTZ', 'YYYYMMDDHHMISS', numeric_date)

Example 8

The following does a full calculation of times. It goes from a source date column named dt to a target column named dt5 that is to be converted to the date + 5 hours. The calculation also goes from a source timestamp column named ts to a target column named ts5 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 ) )
) ;