15.10 - FORMAT Phrase and DateTime Formats - Teradata Database

Teradata Database SQL Data Types and Literals

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

The date and time formatting characters in a FORMAT phrase determine the output of DATE, TIME, and TIMESTAMP information.

 

IF the data type is …

THEN use formatting characters for …

DATE

PERIOD(DATE)

date information.

For Period types, the specified format is associated with both the beginning and ending bounds of the period.

TIME

TIME WITH TIME ZONE

PERIOD(TIME)

time information.

For Period types, the specified format is associated with both the beginning and ending bounds of the period.

TIMESTAMP

TIMESTAMP WITH TIME ZONE

PERIOD(TIMESTAMP)

date and time information.

Date formatting characters must be grouped separately from time formatting characters.

For Period types, the specified format is associated with both the beginning and ending bounds of the period.

Formatting characters are case insensitive.

Use the following characters in the FORMAT phrase to control formatting of date information in DATE, PERIOD(DATE), PERIOD(TIMESTAMP), and TIMESTAMP types:

 

Characters

Meaning

MMMM

M4

Represent the month as a full month name, such as November.

Valid names are specified by LongMonths in the current SDF.

M4 is equivalent to MMMM, and is preferable to allow for a shorter, unambiguous format string.

You cannot specify M4 in a format that also has M3 or MM.

MMM

M3

Represent the month as an abbreviated month name, such as 'Apr' for April.

Valid names are specified by ShortMonths in the current SDF.

M3 is equivalent to MMM, and is preferable to allow for a shorter, unambiguous format string.

You cannot specify MMM in a format that also has MM.

MM

Represent the month as two numeric digits.

DDD

D3

Represent the date as the sequential day in the year, using three numeric digits, such as '032' as February 1.

D3 is equivalent to DDD, and allows for a shorter format string.

You cannot specify DDD or D3 in a format that also has DD.

DD

Represent the day of the month as two numeric digits.

YYYY

Y4

Represent the year as four numeric digits.

Y4 is equivalent to YYYY, and allows for a shorter format string.

You cannot specify YYYY or Y4 in a format that also has YY.

YY

Represent the year as two numeric digits.

EEEE

E4

Represent the day of the week using the full name, such as Thursday.

Valid names are specified by LongDays in the current SDF.

E4 is equivalent to EEEE, and allows for a shorter format string.

EEE

E3

Represent the day of the week as an abbreviated name, such as 'Mon' for Monday.

Valid abbreviations are specified by ShortDays in the current SDF.

E3 is equivalent to EEE, and allows for a shorter format string.

/

Slash separator.

Copied to output string where it appears in the FORMAT phrase. This is the default separator for Teradata dates.

B

b

Blank representation separator.

Use this instead of a space to represent a blank.

,

Comma separator.

Copied to output string where it appears in the FORMAT phrase.

Apostrophe separator.

Copied to output string where it appears in the FORMAT phrase.

:

Colon separator.

Copied to output string where it appears in the FORMAT phrase.

.

Period separator.

Copied to output string where it appears in the FORMAT phrase.

-

Dash separator.

Copied to output string where it appears in the FORMAT phrase. This is the default separator for ANSI dates.

9

Decimal digit.

This formatting character can only be used with separators less than 0x009F.

The 9(n) notation can be used for more than one occurrence of this character, where n is an integer constant and means that the '9' repeats n number of times.

This formatting character is for DATE and PERIOD(DATE) types only and cannot appear as a date formatting character for PERIOD(TIMESTAMP) and TIMESTAMP types.

Z

Zero-suppressed decimal digit.

This formatting character can only be used with separators less than 0x009F.

The Z(n) notation can be used for more than one occurrence of this character, where n is an integer constant and means that the 'Z' repeats n number of times.

This formatting character is for DATE and PERIOD(DATE) types only and cannot appear as a date formatting character for PERIOD(TIMESTAMP) and TIMESTAMP types.

Use the following characters in the FORMAT phrase to control formatting of time information in PERIOD(TIME), PERIOD(TIMESTAMP), TIME and TIMESTAMP types:

 

Characters

Meaning

HH

Represent the hour as two numeric digits.

MI

Represent the minute as two numeric digits.

SS

Represent the second as two numeric digits.

S(n)

S(F)

Number of fractional seconds.

Replace n with a number between 0 and 6, or use F for the number of characters needed to display the fractional seconds precision.

The value of F is resolved during the formatting of the TIME or TIMESTAMP data. The value is obtained from the fractional seconds precision in the declaration of the data type. For example, F is two for the TIME(2) type.

A value of zero for F displays no radix symbol and no fractional precision for the data.

The S(F) formatting characters must follow a D formatting character or a . separator character.

A value of n that is less than the PERIOD(TIME), PERIOD(TIMESTAMP), TIME or TIMESTAMP fractional second precision produces an error.

D

Radix symbol.

The value of RadixSeparator in the current SDF is copied to the output string whenever a D appears in the FORMAT phrase.

Separator characters, such as . or :, can also appear in the FORMAT phrase, but only if they do not match the value of RadixSeparator.

T

Represent time in 12-hour format instead of 24-hour format.

The appropriate time of day, as specified by AMPM in the current SDF is copied to the output string where a T appears in the FORMAT phrase.

Z

Time zone.

The Z controls the placement of the time zone in the output of PERIOD(TIME), PERIOD(TIMESTAMP), TIME and TIMESTAMP data, and can only appear at the beginning or end of the time formatting characters.

For example, the following statement uses a FORMAT phrase that includes a Z before the time formatting characters:

SELECT CURRENT_TIMESTAMP
(FORMAT 'YYYY-MM-DDBZBHH:MI:SS.S(6)');

If the PERIOD(TIME), PERIOD(TIMESTAMP), TIME or TIMESTAMP data contains time zone data, the time zone is copied to the output string. The time zone format is +HH:MI or ‑HH:MI, depending on the time zone hour displacement.

:

Colon separator.

Copied to output string where it appears in the FORMAT phrase. This is the default separator for ANSI time.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a colon.

.

Period separator.

This can also be used to indicate the fractional seconds.

Copied to output string where it appears in the FORMAT phrase.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a period.

-

Dash separator.

Copied to output string where it appears in the FORMAT phrase.

h

Hour separator.

A lowercase h character is copied to the output string.

The h formatting character must follow the HH formatting characters.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a lowercase h character.

m

Minute separator.

A lowercase m character is copied to the output string.

The m formatting character must follow the MI formatting characters.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a lowercase m character.

s

Second separator.

A lowercase s character is copied to the output string.

The s formatting character must follow SS or SSDS(F) formatting characters.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a lowercase s character.

B

b

Blank representation separator.

Use this instead of a space to represent a blank.

This character cannot appear in the FORMAT phrase if the value of RadixSeparator in the current SDF is a blank.

For information on using Kanji date and time markers, see “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.

The following table is a nonexhaustive list of formats that can be used to present an output date, where the data is 85/09/12:

 

FORMAT Phrase

Result

FORMAT 'YY/MM/DD'

85/09/12

FORMAT 'DD-MM-YY'

12-09-85

FORMAT 'YYYY/MM/DD'

1985/09/12

FORMAT 'YYYY-MM-DD'

1985-09-12

FORMAT 'YYYY.DDD'

1985.225

FORMAT 'YYBDDD'

85 225

FORMAT 'DDBMMMBYYYY'

12 Sep 1985

FORMAT 'MMMBDD,BYYYY'

Sep 12, 1985

FORMAT 'YYYYBMMMBDD'

1985 Sep 12

FORMAT 'MMM'

Sep

FORMAT 'EEE,BM4BDD,BYYYY'

Thu, September 12, 1985

FORMAT 'E4,BMMMMBDD,BYYYY'

Thursday, September 12, 1985

FORMAT 'E4BDDBM4BYYYY'

jeudi 12 septembre 1985

(jeudi is French for Thursday and septembre is French for September.)

FORMAT '999999'

850912

The following table is a nonexhaustive list of formats that can be used to present an output time, where the data is 13:20:53.64+03:00:

 

FORMAT Phrase

Result

FORMAT 'HH:MIBT'

01:20 PM

FORMAT 'HH:MI'

13:20

FORMAT 'HH.MI.SS'

13.20.53

FORMAT 'HH:MI:SSBT'

01:20:53 nachm

(nachm is German for PM.)

FORMAT 'HH:MI:SSDS(F)'

13:20:53.64

FORMAT 'HH:MI:SSDS(F)Z'

13:20:53.64+03:00

FORMAT 'HHhMImSSs'

13h20m53s

The following table is a nonexhaustive list of formats that can be used to present an output timestamp, where the data is 85/09/12 13:20:53.64+03:00:

 

FORMAT Phrase

Result

FORMAT 'MM/DD/YYBHH:MIBT'

09/12/85 01:20 PM

FORMAT 'MMMBDD,BYYBHH:MI:SS'

Sep 12, 85 13:20:53

FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)'

Thu, September 12, 1985 13:20:53.64

FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)Z'

1985-09-12 13:20:53.64+03:00

If a field is declared as type DATE and a FORMAT phrase is specified for it, the date must be entered in the specified format and it must be enclosed in apostrophes.

For example, data supplied to, or used in, a conditional WHERE or HAVING clause for the following column must be defined as 'OCT 24, 1985'.

   DOB DATE FORMAT 'MMMbDD,bYYYY'

This format consistency restriction applies only to the date values themselves, and not to the date separators. The input date separators need not match the FORMAT phrase date separators. In addition, you can enter a 4 digit year even if the FORMAT phrase specifies the year as 'YY'.

If a field is to be inserted into a DATE column, the format must match either the ANSI DATE literal format or the format of the column. If a field is to be compared with a DATE value, the format must match one of the following:

  • ANSI DATE literal format
  • Format of the DATE value
  • DATE format determined by DateForm and default DATE data type format in the SDF. See “DATE Formats” on page 285.
  • For example, the comparison works if the data is CHAR(8) in the form YY/MM/DD and a DATE column format is YY/MM/DD. The comparison fails, however, if the column format is YYYY-MM-DD.

    To perform comparisons that do not meet these qualifications, convert the values as described in SQL Functions, Operators, Expressions, and Predicates.

    Use a statement like the following to display a date in uppercase:

       SELECT DATE (FORMAT 'MMMbdd,bYYYY') (CHAR(12), UC);

    Using 1985-09-12 for data, this statement returns:

       SEP 12, 1985

    The following query shows how to specify a date if, for example, the p_date column was specified as FORMAT ' DDBMMMBYYYY ':

       SELECT * 
       FROM sales 
       WHERE p_date = '30 Mar 1994';

    If the p_date column was specified as FORMAT ' YYYY‑MM‑DD ', the query would be as follows:

       SELECT * 
       FROM sales 
       WHERE p_date = '1994-03-30';

    Create a table using the FORMAT phrase to specify the output format:

       CREATE TABLE t1
       (f1 TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)'
       ,f2 TIMESTAMP(4) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z');

    Populate the table using TIME and TIMESTAMP literals.

       INSERT t1 (TIME '10:44:25.123-08:00', 
       TIMESTAMP '2000-09-20 10:44:25.1234');

    Query the data:

       SELECT f1, f2 
       FROM t1;

    The query returns:

                 f1                       f2
       ------------ ------------------------
       10:44:25.123 2000-09-20 10:44:25.1234

    Column f1 was defined as a TIME WITH TIME ZONE data type, but the FORMAT did not include the Z formatting character, so the time zone does not appear in the output. Column f2 was defined as a TIMESTAMP data type, and the FORMAT includes the Z formatting character. However the timestamp data is not associated with any time zone, so no time zone information appears in the output.

    To display the time zone information for column f1, use the FORMAT phrase in the SELECT:

       SELECT CAST (f1 AS TIME(3) WITH TIME ZONE FORMAT 'HH:MI:SS.S(F)Z')
       FROM t1;

    The query returns:

                      f1
       -----------------
       10:44:25.123-8:00

    For more information on TIME and TIMESTAMP literals, see Chapter 2: “Data Literals.”

    For more information on using the FORMAT phrase in data type conversions, see SQL Functions, Operators, Expressions, and Predicates.

    When data is imported in Record Mode into a NULLABLE DATE field, and the source data is a binary integer of value zero, then the field is set to NULL (not zero).