15.10 - FORMAT Phrase, DateTime Formats, and Japanese Character Sets - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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’
‘YY’

Year

U+6708 ( )

‘MM’

Month

U+65E5 ( )

‘DD’

Day

For example, apply (FORMAT ‘YYMMDD’) 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 ‘MMYYDD’).

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 ‘MMDD’) is incorrect and an error is generated.

Applying (FORMAT ‘YYMMDD’) to 920310 yields 040310, 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 ‘999999’) 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:

  • If the day and month have not been indicated, the day is set to the first day of that era (for example, day 25 for Showa era) and the month is set to the first month of that era (for example, month 12 for Showa era).
  • If the day is indicated but the month is not, the month is set to the first month of that 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.

  • If the month is indicated but the day is not
  • If it is the first month of the era (for example, month 12 for the Showa era), then day is set to the first day of that era.
  • If it is not the first month of the era, the day is set to 1 (the beginning of the month).