17.10 - SDF File - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

The SDF file is a text file that defines how Vantage formats numeric, date, time, and currency output. The formatting strings it contains are used in conjunction with special formatting characters used in SQL FORMAT output phrases.

Vantage includes a default SDF file that contains a viewable and editable text version of the default output formatting settings. The default SDF file is located in /usr/tdbms/etc.

The SDF file controls how the following kinds of information are formatted in the output of Vantage.
  • Day names
  • Month names
  • AM and PM names
  • Numeric and currency separators
  • Numeric and currency digit grouping rules
  • Currency symbols
  • Default display formats for data types
The SDF file also controls the default display formats for the following data types:
  • BYTEINT
  • SMALLINT
  • BIGINT
  • INTEGER
  • NUMERIC (includes DECIMAL)
  • REAL (includes DOUBLE PRECISION and FLOAT)
  • DATE
  • TIME and TIME WITH TIME ZONE
  • TIMESTAMP and TIMESTAMP WITH TIME ZONE
  • NUMBER

The SDF file also can be used to specify a time zone string, which identifies time zone the server will use, and can specify rules for when Daylight Saving Time begins and ends for time zones that observe DST.

You cannot use the SDF file to control output formatting for the INTERVAL data type.

The formatting characters permitted in the format strings of the SDF file are the same formatting characters permitted in an SQL FORMAT for the data types listed above. You can override the default display format for a data type in the SDF file by using the FORMAT output phrase in a CREATE TABLE, ALTER TABLE, or SELECT statement in SQL.

SDF quoted strings are delimited by the quotation mark (U+0022) and can specify almost any Unicode character accepted by Vantage. However note the following restrictions:
  • Characters within SDF quoted strings are limited to the printable 7-bit ASCII characters (U+0020, U+0021 and U+0023 through U+007E). The quotation mark itself (U+0022) cannot be used within an SDF quoted string.
  • The reverse solidus (\) character(U+005C) does not represent itself, but instead can be used to specify Unicode characters above U+009F. The sequence \u XXXX specifies the corresponding Unicode character U+ XXXX, where XXXX is the hexadecimal for the Unicode character.

Formats are restricted to valid formats as specified in Teradata Vantage™ - Data Types and Literals, B035-1143.

For example, you can set the value of the currency string in the SDF file to the currency symbol native to your locale. To include that currency symbol when you display numeric monetary information in an SQL SELECT statement, use the L formatting character in the FORMAT output phrase. For more information on display formats and SQL output format phrases, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Additionally, the SDF file can be used to specify a time zone string, which allows Vantage to automatically adjust the system time for locales that use Daylight Saving Time.

SDF Elements

The SDF file uses a simple format where each entry is an SDF element that specifies the output formatting for one kind of data, or specifies a time zone string. The generic format of an SDF element is:

KEYWORD {value_list}

where value_list is one or more values delimited by double-quote characters and separated by semicolons.

Keywords are not case sensitive, but values are, unless stated otherwise. The SDF format strings consist of printable characters from 7-bit ASCII, unless an element is restricted further by the type of data. For out-of-range characters, use the \u Unicode hex notation. The SDF can contain single-line comments that start with //.

All elements must be specified once in the SDF file, so it is convenient to start from an existing SDF file and edit to suit your needs.

The following table described the individual SDF elements.

SDF Element Keyword Value Description
ShortDays Seven abbreviated names for the days of the week in the native language.

These names can vary in length. There must be seven values entered for this keyword.

Example:

ShortDays {"Sun";"Mon";"Tue";"Wed";"Thu";
"Fri";"Sat"}
LongDays Seven full names for the days of the week in the native language.

The order of the names must match the order of the names of the ShortDays. These names can vary in length. There must be seven values entered for this keyword.

Example:

LongDays {"Sunday";"Monday";"Tuesday";
"Wednesday";"Thursday";"Friday";
"Saturday"}
ShortMonths Twelve abbreviated names for the months of the year in the native language.

These names can vary in length.There must be twelve values entered for this keyword.

Example:

ShortMonths {"Jan";"Feb";"Mar";"Apr";
"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}
LongMonths Twelve full names for the months of the year in the native language.

The order of the names must match the order of the names of the ShortMonths. These names can vary in length. There must be twelve values entered for this keyword.

Example:

LongMonths {"January";"February";"March"; "April";"May";"June";"July";"August"; "September";"October";"November";
"December"}
AMPM The abbreviated names for AM (ante meridiem), and PM (post meridiem) in the native language.

These names can vary in length. There must be two values entered for this keyword.

Example:

AMPM {"AM";"PM"}
RadixSeparator The character that separates the integer and fractional parts of non-monetary strings. Separators cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits (0 through 9)
  • E
  • e
  • V
  • v

The RadixSeparator value must be different from the GroupSeparator value. However, the RadixSeparator value can be the same as the CurrencyRadixSeparator value.

Example:

RadixSeparator {"."}
GroupSeparator The character that separates groups of digits in the integer part of non-monetary strings.
Separators cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits (0 through 9)
  • E
  • e

The GroupSeparator value can be the same as the CurrencyGroupSeparator value.

Example:

GroupSeparator {","}
GroupingRule A group of numbers that defines the size of each group of digits in non-monetary strings.
The size of each group can vary. Each number specifies how many digits are in each group with the initial integer defining the size of the group immediately preceding the radix separator, and the following integers defining groups preceding that.
  • If the last integer is -1, no further grouping shall be performed.
  • If the last integer is not -1, the size of the previous group (if any) shall be repeatedly used for the remainder of the digits.
  Examples:

GroupingRule {"3"}

For GroupingRule {“3”}, the numeric value 123456789.00 is formatted as 123,456,789.00.

GroupingRule {"3,2,-1"}

For GroupingRule {“3,2,-1”}, the numeric value 123456789.00 is formatted as 1234,56,789.00.

Currency The string used to represent the local currency.
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Examples of dollar, yen, and euro currency strings:

Currency {"$"}
Currency  {"\u00A5"} 
Currency  {"\u20AC"}
ISOCurrency String that represents the local currency as an uppercase three-character code from ISO 4217. (You should verify that the string is specified in ISO 4217.)
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Example:

ISOCurrency {"USD"}
CurrencyName String that represents the local currency as a completely spelled out currency name.
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Example:

CurrencyName {"US Dollars"}
CurrencyRadixSeparator The character that separates the integer and fractional part of monetary strings.
Separators cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • E
  • e
  • V
  • v

The value must be different that the value of CurrencyGroupSeparator.

Example:

CurrencyRadixSeparator {"."}
CurrencyGroupSeparator The character that separates groups of digits in the integer part of monetary strings.
Separators cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • E (by itself)
  • e (by itself)

The value must be different that the value of CurrencyRadixSeparator.

Example:

CurrencyGroupSeparator {","}
CurrencyGroupingRule A group of numbers that defines the size of each group of digits in monetary strings.

The size of each group can vary. Each number specifies how many digits are in each group with the initial integer defining the size of the group immediately preceding the radix separator, and the following integers defining groups preceding that.

Example:

CurrencyGroupingRule {"3"}
DualCurrency String that represents the dual currency character.
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Example:

DualCurrency {"$"}
DualISOCurrency String that represents the dual currency as a three-character code from ISO 4217.
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Example:

DualISOCurrency {"USD"}
DualCurrencyName String that represents the dual currency as a completely spelled out currency name.
Currencies cannot contain the following:
  • plus sign (+)
  • minus sign (-)
  • digits
  • CurrencyRadixSeparator
  • CurrencyGroupSeparator
  • E (by itself)
  • e (by itself)
  • percent sign (%)
  • comma (,)
  • dot (.)
  • slash (/)
  • colon (:)

Example:

DualCurrencyName {"US Dollars"}
BYTEINT String that represents the default format applied to BYTEINT data types.

Example:

BYTEINT {"-(3)9"}
INTEGER String that represents the default format applied to INTEGER data types.

Example:

INTEGER {"-(10)9"}
SMALLINT String that represents the default format applied to SMALLINT data types.

Example:

SMALLINT {"-(5)9"}
BIGINT String that represents the default format applied to BIGINT data types.

Example:

BIGINT {"-(19)9"}
NUMERIC String that represents the default format applied to NUMERIC and DECIMAL data types.

Example:

NUMERIC {"--(I).9(F)"}
  • I represents the number of characters needed to display the integer portion of NUMERIC and INTEGER data. For example:
    • With INTEGER type, I is 10.
    • With DECIMAL type (10,2), I is 8
  • F represents the number of characters required to display the fractional portion of NUMERIC data. For example, with DECIMAL type (10,2), F is 2.
REAL String that represents the default format applied to REAL, DOUBLE PRECISION, and FLOAT data types.

Example:

REAL {"-9.99999999999999E-999"}
DATE String that represents the default format applied to DATE and PERIOD(DATE) data types.

Example:

DATE {"YY/MM/DD"}
TIME String that represents the default format applied to TIME, TIME WITH TIME ZONE, and PERIOD(TIME) data types.

Example:

TIME {"HH:MI:SS.S(F)Z"}
TIMESTAMP String that represents the default format applied to TIMESTAMP, TIMESTAMP WITH TIME ZONE, and PERIOD(TIMESTAMP) data types.

Example:

TIMESTAMP {"YYYY-MMDDBHH:MI:SS.S(F)Z"}
TimeZoneString TimeZoneString names a time zone or GMT offset.

The value is a sequence of strings that represents the time offset for the time zone.

Example:

{"GMT-8"; "-8"; "0"}

For locations that observe Daylight Savings Time (DST), the string can define the set of rules for when in the calendar year the system time zone offset should change. The system will automatically adjust for DST according to this code. For example:

Example:

{"America Pacific"; "-8"; "0"; "2"; "4"; "4"; "1"; "0"; "0"; "02:00:00"; "3"; "10"; "0"; "0"; "-1"; "02:00:00"; "1987"; "2006"; "-8"; "0"; "-7"; "0"; "4"; "3"; "8"; "0"; "0"; "02:00:00"; "4"; "11"; "1"; "0"; "0"; "02:00:00";"2007"; "9999"; "-8"; "0"; "-7"; "0"}
Rules in sample time zone strings cover years from 1987 to 9999. If you need the rules to cover years prior to 1987, you can modify the samples and add additional rules.

For more information on time zone strings and time zone rules, see Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.

Also refer to the sample time zone strings and rules in the file tdlocaledef_tzrules.txt, which is in /usr/tdbms/etc.

NUMBER String the represents the default format applied to NUMBER data types.

Example:

NUMBER {"FN9"}