Qlik Replicate: Mapping Oracle TIMESTAMP(6) WITH TIME ZONE to SQL Server Data Types

[john_wang]

By default, the Oracle data type TIMESTAMP(6) WITH TIME ZONE is mapped to VARCHAR(38) in the SQL Server target when using Qlik Replicate. However, in some cases, you may prefer to preserve a more compatible datetime format on the SQL Server side. Below are two workarounds to achieve this:

Map to DATETIMEOFFSET(6) in SQL Server

You can map TIMESTAMP(6) WITH TIME ZONE to DATETIMEOFFSET(6) using the following transformation to trim the input:

substr($TZ, 1, 26)

This transformation will remove the time zone information.

For example, the source value "2025-04-18 14:43:06.000000000 +08:00" will become "2025-04-18 14:43:06.000000".

Without applying this transformation, Qlik Replicate may raise an error:

Invalid character value specified for cast

Map to DATETIMEOFFSET(7) in SQL Server

To retain both the full precision and the time zone, map the Oracle data type to DATETIMEOFFSET(7) and use the following transformation:

substr($TZ, 1, 27) || substr($TZ, 30, 7)

This approach preserves both the 7-digit fractional seconds and the time zone.

For example, the Oracle source value "2025-04-18 14:43:06.000000000 +08:00" will be converted to "2025-04-18 14:43:06.0000000 +08:00" on the SQL Server side.

Environment

• Qlik Replicate, all versions
• Oracle Server, all versions
• Microsoft SQL Server, all versions