Date conversion—%TODATE

Syntax

%TODATE(date, type)

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.

Note the following considerations about the values returned by this function:

• The length indicated in the following table represents the number of digits after the separators have been removed
• If you specify NULL for date, this function returns NULL.
• If you specify 0 for date, this function returns 1901-01-01.
• If you specify a DATE for date, this function returns a DATE

Note: If you specify - and / characters, these characters are removed before the value is evaluated by CDC Replication.

Data type of date value	type value	Length of date	Input format
Numeric	*YMD	6 digits	YYMMDD
Character	*YMD	6 digits	YY-MM-DD or YY/MM/DD
Numeric	*MDY	6 digits	MMDDYY
Character	*MDY	6 digits	MM-DD-YY or MM/DD/YY
Numeric	*DMY	6 digits	DDMMYY
Character	*DMY	6 digits	DD-MM-YY or DD/MM/YY
Numeric	*YYMD	8 digits	CCYYMMDD
Character	*YYMD	8 digits	CCYY-MM-DD or CCYY/MM/DD
Numeric	*CYMD	7 digits	CYYMMDD
Numeric	*JUL	5 digits	YYJJJ
Numeric	*CJUL	6 digits	CYYJJJ
Numeric	*YJUL	7 digits	CCYYJJJ

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 %TODATE function returns the corresponding year in the 20th century. For example, 1940. If you specify a value for yy between 0 and 39, the %TODATE 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.

Result data type