The FORMAT phrase can present the date and time in Japanese format.
FORMAT allows symbols like SLASH and COMMA as separators between day, month, and year. Non-Kanji characters in the FORMAT clause are halfwidth.
For example, applying (FORMAT ‘YY/MM/DD’) to 920310 yields 92/03/10.
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’ |
Year |
|
U+6708 ( |
‘MM’ |
Month |
|
U+65E5 ( |
‘DD’ |
Day |
For example, apply (FORMAT ‘YY
MM
DD
’) to 920310 to yield 920310.
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 ‘YYMMDD’), (FORMAT ‘YYDDMM’), and (FORMAT ‘YYDD’) are valid, but (FORMAT ‘MM
DD’) is incorrect and an error is generated.
Applying (FORMAT ‘YYMMDD’) to 920310 yields 
04
0310, where are the two ideographs that together indicate the Heisei era, and , and are the NEN, GATSU, and NICHI ideographs respectively (04 stands for fourth year
of Heisei era).
Kanji characters are valid in the FORMAT clause as markers for time information in TIME and TIMESTAMP data types. The following table lists the appropriate markers, their Kanji characters, and their description.
|
Marker |
Kanji Character |
Description |
|
JI |
U+6642 ( |
Hours |
|
FUN |
U+5206 ( |
Minutes |
|
BYOU |
U+79D2 ( |
Seconds |
For example, applying (FORMAT ‘99
99
99
’) to 010203 yields 010203.
You can use Kanji characters with fractional seconds in TIME and TIMESTAMP formatting. For example:
CREATE TABLE t3
(tstart TIME(2) FORMAT 'HHMISS.S(2)'
,tend TIME FORMAT 'HHMISSDS(F)');
Time data must include the same Kanji time markers that appear in the FORMAT phrase. For example, the following CREATE TABLE statement uses the FORMAT phrase to define the display format for a TIMESTAMP column.
CREATE TABLE t2
(ts TIMESTAMP FORMAT 'YYMMDDHHMISS');
The following INSERT statement is valid, because the character string data includes the Kanji time markers specified in the FORMAT phrase.
INSERT t2 ('920310180623');
FORMAT can also be used for input.
For example, the following statement creates a table table_2 with two date type columns:
CREATE TABLE table_2
(f1 DATE FORMAT 'YYMMDD',
f2 DATE FORMAT 'YYMMDD');
The following SQL statement is valid:
INSERT INTO table_2 ('920310', '040310');
Each Imperial Era (except the current one) has an end, which is considered in the validation of input. Using Japanese Imperial Era formatting for dates previous to the Meiji era (before 1867/1/9) is considered a run-time error.
Normally on input, Teradata Database assigns default values for the year (current year), month (current month), and day (first day), if not specified.
For the first year of an era, the default month and day is affected by the starting date of the era. For other years, the standard default of the first month and first day are applied.
The following rules are applied for the first year of an era:
Note that this can produce an error.
For example, the date ‘0106’ indicates the first year of the Heisei era and day 6. The month is defaulted to
the first month of the Heisei era (January).
This yields the date 1989/1/6, which is not a valid date in the Heisei era.