Formatting Characters for Time Information | Teradata Vantage - Formatting Characters for Time Information - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.