Date and time conversion—%TODATETIME

Use this function when you want CDC Replication to convert a numeric or character data type to a datetime data type during replication.

You can convert dates from packed-numeric, zoned-numeric, or from character formats without century, to datetime or character-type values with century. You can also convert time data types from packed-numeric, zoned-numeric, or character formats into datetime or character-type values with century.
Note: The CDC Replication Engine for Db2® for i does not support this function.

Syntax

%TODATETIME (date, type, time)

Parameters

date
Specifies the input date. If date is the name of a column containing a character string, the length of that string must match the length for the format specified by the type value. CDC Replication generates an error if the length is any other value.
Note the following about the values returned by this function:
  • If you specify NULL for date, this function returns NULL.
  • If CDC Replication encounters an error when parsing date, this function returns 1901-01-01.
  • If you specify DATE for date, this function returns a timestamp.
Note: If you specify - and / characters, these characters are removed before the value is evaluated by CDC Replication.
Table 1. Date input formats
type value Length of date
*YMD (yymmdd) 6 digits
*MDY (mmddyy) 6 digits
*DMY (ddmmyy) 6 digits
*YYMD (ccyymmdd) 8 digits
*CYMD (cyymmdd) 7 digits
*JUL (yyjjj) 6 digits
*CJUL (cyyjjj) 6 digits
*YJUL (ccyyjjj) 7 digits
type
Specifies the format of the input date. You must enclose values of this parameter in double quotation marks.
*YMD
Specifies the input format is yymmdd.
*MDY
Specifies the input format is mmddyy.
*DMY
Specifies the input format is ddmmyy.
*YYMD
—Specifies the input format is ccyymmdd, where cc represents the century.
*CYMD
Specifies the input format is cyymmdd, where c represents the century. A value of 0 for c represents the 20th century. Any other value represents the 21st century.
*JUL
Specifies the input format is yyjjj, where jjj represent the sequence number of a day in the calendar year. jjj must be between 1, which represents January 1st, and 366, which represents December 31st in a leap year. For jjj values less than 100, you must specify the leading zero or zeros. For example, the Julian date for February 4th is 035, which represents the 35th day of the year.
When you set type to *JUL, if you specify a value for yy between 40 and 99, the %TODATETIME function returns the corresponding year in the 20th century. For example, 1940. If you specify a value for yy between 0 and 39, the %TODATETIME function returns the corresponding year in the 21st century. For example, 2039.
*CJUL
Specifies the input format is cyyjjj, where c represents the century. A value of 0 for c represents the 20th century. Any other value represents the 21st century.
*YJUL
Specifies the input format is ccyyjjj, where cc represents the century.
time
Specifies the input time. The following table indicates the length and format for this parameter, depending on the data type of the input time.
Table 2. Length and format for the time parameter, depending on the data type of the input time
Data type Length Format
Numeric 5 digits HMMSS. For example, 71500 represents 7:15 AM.
6 digits HHMMSS. For example, 223000 represents 10:30 PM.
Character 8 digits HH:MM:SS. You must enclose values of this parameter in double quotation marks. For example, 10:30:00 represents 10:30 AM.

Result data type

Date, in standard ISO (International Organization for Standardization) format. If the input date contains an invalid value for the year, month, or day, the %TODATETIME function returns the default value 1901-01-01 for the date.

Examples

The following table provides examples for this function. These examples show the colon (:) as the separator in the returned ISO time values. Depending on your environment, a different character may separate the year, the month, and the day in the output date. Also, a different character may separate the hours, the minutes, and the seconds in the output time.
Input date (date) Input format (type) Input time (time) Result
891102 *YMD 112500 1989-11-02 11:25:00 (November 2, 1989 at 11:25 AM)
030496 *MDY 13:42:00 1996-03-04 13:42:00 (March 4, 1996 at 1:42 PM)
000000 *MDY 10:55:00 1901-01-01 10:55:00 (January 1, 1901 at 10:55 AM)
210570 *DMY 09:05:00 1970-05-21 09:05:00 (May 21, 1970 at 9:05 AM)
20100902 *YYMD 023000 2010-09-02 02:30:00 (February 9, 2010 at 2:30 AM)
1060723 *CYMD 193300 2006-07-23 19:33:00 (July 23, 2006 at 7:33 PM)
91060 *JUL 220100 1991-03-01 22:01:00 (March 1, 1991 at 10:01 PM)
097106 *CJUL 043500 1997-04-16 04:35:00 (April 16, 1997 at 4:35 AM)
2002092 *YJUL 17:15:00 2002-04-02 17:15:00 (April 2, 2002 at 5:15 PM)