StarQuest Technical Documents

How to Replicate DB2 Date and Time Data Types to the SQL Server Datetime or Smalldatetime Data Types

Last Update: 07 February 2005
Product: StarQuest Data Replicator
Version: 3.x
Article ID: SQV00DR004

Abstract

This document explains how to replicate from DB2 Date and Time data types to the SQL Server Datetime or Smalldatetime data types.

Solution

SQL Server does not provide equivalents for the DB2 data types Date and Time. For Date source column types, SQDR sets the default destination data type to Char(10). For Time source column types, SQDR sets the default destination data type to Char(8).

Manually change the destination data type to the SQL Server data type Datetime or Smalldatetime to replicate the data into a more convenient form. Your choice between destination data types of Datetime or Smalldatetime and Char should be based on the form and precision of the data your application needs.

In the Column properties dialog of the subscription, highlight the Date or Time column and click the Modify Column button. Select Datetime or Smalldatetime from the Data Type drop down list and click OK.

SQDR will handle the difference between Date and Time sources and Datetime destination column structures as follows:

  • When a DB2 Date column is replicated to a SQL Server Datetime column, the time portion of the destination value is set to 00:00:00.000.
  • When a DB2 Time column is replicated to a SQL Server Datetime column, the date portion of the destination value is set to the current system date.

SQDR will handle the difference between source and Smalldatetime destination column structures as follows:

  • When a DB2 Date column is replicated to a SQL Server Smalldatetime column, the time portion of the destination value is set to 00:00:00.
  • When a DB2 Time column is replicated to a SQL Server Smalldatetime column, the date portion of the destination value is set to the current system date.

Examples:

Source DB2 data type Destination SQL Server Datetime Destination SQL Server Smalldatetime
Date:  1979-01-13 1979-01-13 00:00:00.000 1979-01-13 00:00:00
Time:  11:42:26 2005-02-07 11:42:26.000 2005-02-07 11:42:00

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.