IBM i: The SQL Way · #9

Convert an SQL Timestamp to an Internal IBM i System Timestamp

Use TO_SYSTEM_TIMESTAMP to convert an SQL date or timestamp into the eight-byte internal timestamp format used by selected IBM i interfaces.

Some IBM i interfaces expect time to be supplied in an eight-byte internal system format rather than as an SQL TIMESTAMP. TO_SYSTEM_TIMESTAMP creates that internal value from an SQL date, timestamp, or valid timestamp string.

The function is:

TO_SYSTEM_TIMESTAMP

It accepts:

DATE
TIMESTAMP
character timestamp representation
graphic timestamp representation

and returns:

CHAR(8) FOR BIT DATA

Basic example

VALUES TO_SYSTEM_TIMESTAMP(
    CURRENT TIMESTAMP
);

The result is binary data. Display it in hexadecimal form:

VALUES HEX(
    TO_SYSTEM_TIMESTAMP(
        CURRENT TIMESTAMP
    )
);

A hexadecimal representation is easier to inspect and copy during testing.

Convert a known timestamp

VALUES HEX(
    TO_SYSTEM_TIMESTAMP(
        TIMESTAMP('2026-01-05-11.52.04.417012')
    )
);

To verify the result, convert it back:

VALUES FROM_SYSTEM_TIMESTAMP(
    TO_SYSTEM_TIMESTAMP(
        TIMESTAMP('2026-01-05-11.52.04.417012')
    )
);

Convert a date

When the argument is a DATE, IBM converts it to a timestamp at midnight:

VALUES HEX(
    TO_SYSTEM_TIMESTAMP(
        DATE('2026-07-26')
    )
);

The logical timestamp is:

2026-07-26 00:00:00

Convert a formatted date string

When the source text is not already in a directly accepted timestamp representation, use TIMESTAMP_FORMAT first:

VALUES HEX(
    TO_SYSTEM_TIMESTAMP(
        TIMESTAMP_FORMAT(
            '07/26/2026 14:30:45',
            'MM/DD/YYYY HH24:MI:SS'
        )
    )
);

Separating parsing from conversion makes the logic clearer:

Text
  ↓ TIMESTAMP_FORMAT
SQL TIMESTAMP
  ↓ TO_SYSTEM_TIMESTAMP
Internal system timestamp

Insert into a binary-compatible column

Assume an application table stores the internal value:

INSERT INTO MYLIB.SYSTEM_EVENT
(
    EVENT_ID,
    INTERNAL_EVENT_TIMESTAMP
)
VALUES
(
    1001,
    TO_SYSTEM_TIMESTAMP(
        CURRENT TIMESTAMP
    )
);

The target column should preserve the eight bytes without character conversion.

A suitable SQL definition could be:

INTERNAL_EVENT_TIMESTAMP
    CHAR(8) FOR BIT DATA

Update a value from an SQL timestamp column

UPDATE MYLIB.SYSTEM_EVENT
SET INTERNAL_EVENT_TIMESTAMP =
    TO_SYSTEM_TIMESTAMP(EVENT_TIMESTAMP)
WHERE EVENT_ID = 1001;

Use this only when an IBM i interface or established file format requires the internal representation.

For ordinary application timestamps, prefer a normal SQL TIMESTAMP column.

Supported date range

IBM documents the supported date range as:

1928-08-24 through 2071-05-09

If the date portion is outside this range, the function returns null.

Example:

VALUES TO_SYSTEM_TIMESTAMP(
    DATE('1900-01-01')
);

The result is null.

This behavior is important because an out-of-range value might otherwise be mistaken for a successfully generated internal timestamp.

Detect out-of-range values

WITH INPUT (SOURCE_TIMESTAMP) AS
(
    VALUES
      TIMESTAMP('1900-01-01-00.00.00')
)
SELECT
    SOURCE_TIMESTAMP,
    TO_SYSTEM_TIMESTAMP(SOURCE_TIMESTAMP)
        AS INTERNAL_TIMESTAMP,
    CASE
        WHEN TO_SYSTEM_TIMESTAMP(SOURCE_TIMESTAMP)
             IS NULL
          THEN 'NOT CONVERTED'
        ELSE 'CONVERTED'
    END AS CONVERSION_STATUS
FROM INPUT;

In application code, treat a null result as a validation failure when the input was not null.

Null behavior

A null input returns null:

VALUES TO_SYSTEM_TIMESTAMP(
    CAST(NULL AS TIMESTAMP)
);

This allows the function to be used naturally in inserts and updates where the timestamp is optional.

SQLRPGLE example

dcl-s EventTs timestamp;
dcl-s InternalTs char(8);

exec sql
   values TO_SYSTEM_TIMESTAMP(:EventTs)
      into :InternalTs;

The RPG variable must preserve the eight-byte result exactly.

Do not trim it, convert it through a text CCSID, or treat hexadecimal display text as the actual binary value.

These are different:

Eight binary bytes
Hexadecimal characters describing those bytes

Do not store the HEX result as the timestamp

This query is useful for display:

VALUES HEX(
    TO_SYSTEM_TIMESTAMP(
        CURRENT TIMESTAMP
    )
);

But it returns a character representation of the bytes.

If an API or table expects the actual eight-byte value, pass:

TO_SYSTEM_TIMESTAMP(...)

not:

HEX(TO_SYSTEM_TIMESTAMP(...))

When should this function be used?

Use it when an IBM i interface explicitly requires an internal system timestamp.

Examples may include:

• an IBM i API parameter
• a system data structure
• a binary application interface
• an existing file whose format is defined that way
• compatibility with a system service or legacy program

Do not use it merely because eight bytes appear smaller than an SQL timestamp.

For application database design, an SQL TIMESTAMP is usually more understandable and easier to query.

Release and PTF requirement

IBM lists TO_SYSTEM_TIMESTAMP as a Db2 for i functional enhancement delivered with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

Confirm the installed level before adding the function to production code.

Round-trip validation

During implementation, test representative values:

WITH TEST_VALUES (SOURCE_TIMESTAMP) AS
(
    VALUES
      TIMESTAMP('2026-01-01-00.00.00.000000'),
      TIMESTAMP('2026-07-26-14.30.45.123456'),
      TIMESTAMP('2071-05-09-23.59.59.000000')
)
SELECT
    SOURCE_TIMESTAMP,
    HEX(
        TO_SYSTEM_TIMESTAMP(SOURCE_TIMESTAMP)
    ) AS INTERNAL_TIMESTAMP_HEX,
    FROM_SYSTEM_TIMESTAMP(
        TO_SYSTEM_TIMESTAMP(SOURCE_TIMESTAMP)
    ) AS CONVERTED_BACK
FROM TEST_VALUES;

This confirms both the binary generation and the readable interpretation.

A practical workflow

1. Confirm the receiving interface requires an internal system timestamp.
2. Validate the source date or timestamp.
3. Check the supported date range.
4. Convert with TO_SYSTEM_TIMESTAMP.
5. Preserve the eight bytes without CCSID conversion.
6. Test with FROM_SYSTEM_TIMESTAMP.
7. Pass the binary value—not its HEX display string—to the interface.

Final takeaway

TO_SYSTEM_TIMESTAMP bridges normal SQL date and timestamp values with IBM i interfaces that use the internal eight-byte timestamp format.

Use it deliberately. For ordinary application data, keep the timestamp as an SQL TIMESTAMP; convert only at the boundary that requires the system representation.