HCOSS Datetime Data Type Handling

While most mainframe DB2 applications use the default format for DB2 DATE, TIME, and TIMESTAMP PIC X character host variables, some do not. Mainframe DB2 applications support alternative formats as well as the defaults. Similarly, most SQL Server applications use the default date, time, and datetime2 formats for PIC X character host variables. Unfortunately, default DB2 data type formats do not match the default SQL Server data type formats. To ensure that your mainframe DB2 DATE, TIME, and TIMESTAMP formatting is properly converted and recognized by your SQL Server application, use HCOSS to customize the conversion.

For example, the default Data Type formats for character host variables are:

Mainframe Data Type Mainframe DB2 format SQL Server Data Type SQL Server format
DATE yyyy-mm-dd date yyyy-mm-dd
TIME hh.mm.ss time hh:mm:ss
TIMESTAMP yyyy-mm-dd-hh.mm.ss.ffffff datetime2 yyyy-mm-dd hh:mm:ss.ffffffff

The character host variable default formats for the DATE data type for mainframe DB2 and the date data type for SQL Server match; therefore, SQL Server recognizes the DB2 format and no conversion is required. However, the default data type formats for TIME/time and TIMESTAMP/datetime2 do not match. Therefore, if your application uses default DB2 TIME and/or TIMESTAMP formats for your character host variables, and you want to use these for your SQL Server time and datetime2 data types respectively, use HCOSS to convert the mainframe DB2 formats such that they are recognized by SQL Server.

Input Host Variables - DETECTDATE

In cases where the DB2 application being converted uses an input host variable format that is not supported by SQL Server by default, use the DETECTDATE HCOSS compiler directive option to convert the DB2 format to a SQL Server-compatible format. DETECTDATE instructs HCOSS to examine the contents of SQL Server character input host variables, looking for data that matches the mainframe format specified for DATE, TIME and TIMESTAMP data.

DATE and TIME Data Types

With respect to DATE and TIME data types processed using only the DETECTDATE directive to address date and time conversion, HCOSS examines the contents of SQL Server character input host variables and identifies fields that match based on the default DATE and TIME DB2 mainframe formats (yyyy-mm-dd and hh.mm.ss respectively).

If you specify the DATE and/or TIME HCOSS directives in addition to DETECTDATE, HCOSS examines the contents of SQL Server character input host variables and identifies fields that match the date and time formats specified by the DATE and TIME directives.

TIMESTAMP Data Types
With respect to the TIMESTAMP data type, DETECTDATE instructs HCOSS to examine input host variables, looking for matches with any of the following DB2 TIMESTAMP formats:
  • yyyy-mm-dd-hh.mm.ss.ffffff
  • yyyy-mm-dd hh.mm.ss.ffffff
  • yyyy-mm-dd hh:mm:ss.ffffff
  • yyyy-mm-ddThh.mm.ss.ffffff
  • yyyy-mm-ddThh:mm:ss.ffffff

When it finds a match, HCOSS translates the data in the input host variable into a format SQL Server understands.

Guidelines for using DETECTDATE
Use of the DETECTDATE directive can create significant processing overhead. To minimize this, we recommend that you follow the guidelines presented in the following usage scenarios:
Scenario DETECTDATE option
My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, but I only use those values in date, time or datetime2 columns in SQL Server. CLIENT
My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, but I only use those values in character columns in SQL Server. Also, my SQL does not use either implicit or explicit characters for date, time or datetime2 data types. Not required. Do not use DETECTDATE, DATE, or TIME HCOSS SQL compiler directive options.
I only use SQLTYPE host variables with date, time and datetime2 columns in SQL Server, and never use PIC X host variables with date, time or datetime2 columns. Not required.1
My DB2 application uses DATE, TIME, and TIMESTAMP values in PIC X input host variables, and I use those values both in character columns and in date, time and datetime2 columns in SQL Server, and my character columns might use data in formats that could be confused with the formats for DATE, TIME, or TIMESTAMP values. SERVER
My application uses DB2 mainframe DATE and/or TIME formats that match the default SQL Server formats, and I use those values in both character columns and/or date columns in SQL Server. Not required. Do not use DETECTDATE, DATE, or TIME HCOSS SQL compiler directive options.
1Optionally, you can use alternative datetime HCOSS SQL compiler directive options.
Note: For complete information on all DETECTDATE options, see the DETECTDATE HCOSS SQL compiler directive option topic.

Output Host Variables

Simply by enabling HCOSS using the DIALECT=MAINFRAME directive, by default HCOSS returns SQL Server datetime and datetime2 data types in the mainframe default format as shown below:

Mainframe Data Type SQL Server Data Type Mainframe Default Format
DATE date yyyy-mm-dd
TIME time hh.mm.ss
TIMESTAMP datetime2 yyyy-mm-dd-hh.mm.ss.ffffff
DATE and TIME Directives
You can also specify the optional DATE and TIME HCOSS directives, which enable you to specify alternative output host variable formats for SQL Server date and time columns, including USA, EUR or JIS.
Note: If you omit the DETECTDATE directive, the DATE and TIME directives affect output format only.
DATE, TIME, and DETECTDATE Directives
If you specify the DATE and/or TIME directives in addition to the DETECTDATE directive, the specified DATE and TIME formats dictate the format of both input and output host variables.

Datetime Literals in SQL Statements

HCOSS also provides support for literals inside of SQL statements. To enable this functionality, code any of the following SQL comments into your DB2 mainframe application, positioned before a literal declaration. These comments tell HCOSS whether the literal value is used with a DATE, TIME, TIMESTAMP or CHAR column in your DB2 database:

  • /*#CHAR*/
  • /*#DATE*/
  • /*#TIME*/
  • /*#TIMESTAMP*/

In particular, the CHAR literal specification can be very helpful when you do not want HCOSS to translate the literal.