2.12 @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 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 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 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

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 the order_taken column into a Julian timestamp, then adds the order_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 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 ) )
) ;