16.10 - Kanji Date Markers - Teradata Database

Teradata Database SQL Data Types and Literals

Teradata Database
June 2017
Programming Reference

The FORMAT phrase can contain the following Kanji date markers to separate day, month, and year information in DATE and TIMESTAMP data types.

Use this ideograph … After these formatting characters … To describe …
U+5E74 (年 ) ‘YYYY’


U+6708 (月) ‘MM’ Month
U+65E5 (日) ‘DD’ Day

For example, apply (FORMAT ‘YY年MM月DD日’) to 920310 to yield 920310 to yield 92年03月10日.

Because Kanji characters have no canonical form when represented by the KANJI1 character data type, the hexadecimal KANJI1 literal cannot be used for the FORMAT clause.

The Kanji year, month, and day designators can appear in the FORMAT clause in any order, for example, (FORMAT ‘MM月YY年DD日')

Date formatting for DATE and TIMESTAMP data types also supports the last four Japanese Imperial Eras, as described in the following table.

Era Kanji Ideographs Start Date
Meiji 明治 1867/1/9
Taisho 大正 1912/7/30
Showa 昭和 1926/12/25
Heisei 平成 1989/1/8

To enable the Japanese Imperial Era formatting, the year designator ‘和暦YY年' must be used in the format clause (U+548C (和) U+66A6 (暦) indicates the WA and REKI ideographs). For example, (FORMAT ‘和暦YY年MM月DD日'), (FORMAT '和暦YY年DD日MM月'), and (FORMAT '和暦YY年DD日') are valid, but (FORMAT '和暦MM月DD日') is incorrect and an error is generated.

Applying (FORMAT ‘和暦YY年MM月DD日') to 920310 yields 平成04年03月10日, where 平成 are the two ideographs that together indicate the Heisei era, and 年, 月, and 日 are the NEN, GATSU, and NICHI ideographs respectively (04 stands for the fourth year of the Heisei era).