Restrictions - Teradata Vantage - Analytics Database
SQL Data Types and Literals
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-11-06
dita:mapPath
uds1628112204571.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
exs1472253032394
lifecycle
latest
Product Category
Teradata Vantage™
Introduction to SQL Data Types and Literals
Changes and Additions
SQL Data Definition
Data Definition in SQL
Syntax
Using Data Definition
Data Definition Phrases
Data Type Phrases
Data Type Classification
Core Data Type Attributes
Storage and Constraint Attributes
Data Type Attributes and Views
Example: Data Type Attribute Carried Over to a View
Example: Overriding the Data Type Attribute Defined in a Base Table
Example: Data Type Attribute Not Carried Over to a View
Data Conversion
Syntax
ANSI SQL-Compliant Data Conversion
Related Information
COMPRESS and DECOMPRESS Phrases
Syntax
Number of Columns that Can Be Compressed
Multi-value Compression (MVC)
Using MVC with CHARACTER or VARCHAR Data Type
Rules for Specifying constant Values
Compress Values of a CHAR or VARCHAR Column in SHOW TABLE
Achieving Maximum Benefit with COMPRESS
Algorithmic Compression (ALC)
Teradata Compression and Decompression Functions
Restrictions
Example: Compression Using MVC with a constant Value
Example: Compression Using MVC with a Multi-valued List
Example: Compression Using MVC on an INTEGER Column
Example: Compression Using ALC Only
Example: Compression Using MVC and ALC
Example: ALC Using the LZCOMP Function
Related Information
Constraint Attributes
Column and Table Constraints
Uniqueness Constraints
Definition
PRIMARY KEY Constraint
UNIQUE Constraint
CHECK Constraints
Usage Notes
Example: CHECK Constraints
Referential Constraints
Usage Notes
Data Literals
Hexadecimal Byte Literals
Numeric Literals
Integer Literals
Hexadecimal Integer Literals
Decimal Literals
Floating Point Literals
NUMBER Literals
Date and Time Literals
DateTime Literals
Date Literals
Time Literals
Timestamp Literals
Interval Literals
INTERVAL YEAR Literals
INTERVAL YEAR TO MONTH Literals
INTERVAL MONTH Literals
INTERVAL DAY Literals
INTERVAL DAY TO HOUR Literals
INTERVAL DAY TO MINUTE Literals
INTERVAL DAY TO SECOND Literals
INTERVAL HOUR Literals
INTERVAL HOUR TO MINUTE Literals
INTERVAL HOUR TO SECOND Literals
INTERVAL MINUTE Literals
INTERVAL MINUTE TO SECOND Literals
INTERVAL SECOND Literals
Period Literals
Character String Literals
Example: Using Pass Through Character Literals
Unicode Delimited Character Literals
Hexadecimal Character Literals
Graphic Literals
Numeric Data Types
BYTEINT Data Type
SMALLINT Data Type
INTEGER Data Type
BIGINT Data Type
DECIMAL/NUMERIC Data Types
Usage Notes
Examples
Related Information
FLOAT/REAL/DOUBLE PRECISION Data Types
Usage Notes
Example: FLOAT Data Type
Related Information
NUMBER Data Type
Usage Notes
Example: NUMBER Data Type
Related Information
Operations on Floating Point Values
Example: Non-Associativity of Floating Point Arithmetic
Example: Comparing Floating Point Values
Rounding
Character and CLOB Data Types
Character Data
CHARACTER Data Type
Usage Notes
Examples
Related Information
VARCHAR Data Type
Usage Notes
Examples
Related Information
CLOB Data Type
Usage Notes
Examples
Related Information
Default Case Specificity of Character Columns
CASESPECIFIC Phrase
Usage Notes
Examples
UPPERCASE Phrase
Usage Notes
Examples
Teradata SQL Character Strings and Client Physical Bytes
CHARACTER SET Phrase
Usage Notes
LATIN Server Character Set
UNICODE Server Character Set
GRAPHIC Server Character Set
KANJISJIS Server Character Set
KANJI1 Server Character Set [Deprecated]
Byte and BLOB Data Types
Data Storage of Byte and BLOB Types
BYTE Data Type
Usage Notes
Example
VARBYTE Data Type
Usage Notes
Example
BLOB Data Type
Usage Notes
Examples
Related Information
LOB Functions
EMPTY_BLOB
Example: EMPTY_CLOB
EMPTY_CLOB
Example: EMPTY_CLOB
DateTime and Interval Data Types
DateTime Fields
Time Zones
Daylight Saving Time
DATE Data Type
Usage Notes
Examples: DATE Data Type
TIME Data Type
Usage Notes
TIMESTAMP Data Type
Usage Notes
Examples: TIMESTAMP Data Type
TIME WITH TIME ZONE Data Type
Usage Notes
TIMESTAMP WITH TIME ZONE Data Type
Usage Notes
INTERVAL YEAR Data Type
Usage Notes
INTERVAL YEAR TO MONTH Data Type
Usage Notes
Example: Creating a Table With INTERVAL YEAR TO MONTH
INTERVAL MONTH Data Type
Usage Notes
Example: Creating a Table with INTERVAL MONTH
INTERVAL DAY Data Type
Usage Notes
Example: Creating a Table With INTERVAL DAY Column
INTERVAL DAY TO HOUR Data Type
Usage Notes
Example: Creating Table With INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE Data Type
Usage Notes
Example: Creating a Table with INTERVAL DAY TO MINUTE Column
INTERVAL DAY TO SECOND Data Type
Usage Notes
Example: Creating a Table With INTERVAL DAY TO SECOND Column
INTERVAL HOUR Data Type
Usage Notes
INTERVAL HOUR TO MINUTE Data Type
Usage Notes
Example: Creating a Table with INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND Data Type
Usage Notes
INTERVAL MINUTE Data Type
Usage Notes
Example: Creating a Table With INTERVAL_MINUTE
INTERVAL MINUTE TO SECOND Data Type
Usage Notes
Example: Creating a Table With INTERVAL MINUTE TO SECOND
INTERVAL SECOND Data Type
Usage Notes
Example: Creating a Table With INTERVAL SECOND
Period Data Types
Period Data Types: Basic Definitions
Period Type Input and Output Parameters
Related Information
PERIOD(DATE) Data Type
Usage Notes
Examples
Related Information
PERIOD(TIME) Data Type
Usage Notes
Example
Related Information
PERIOD(TIME WITH TIME ZONE) Data Type
Usage Notes
Example
Related Information
PERIOD(TIMESTAMP) Data Type
Usage Notes
Example
Related Information
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
Usage Notes
Example
Related Information
ARRAY/VARRAY Data Type
ANSI Compliance
Syntax
One-Dimensional (1-D) ARRAY Data Type
Multidimensional (n-D) ARRAY Data Type
Privileges Required for Creating an ARRAY Data Type
Creating an ARRAY Data Type
Supported Data Types for ARRAY Elements
Autogenerated Functionality for an ARRAY Data Type
Rules and Guidelines
ARRAY Functions, Operators, and Expressions
ARRAY Type Input and Output Parameters
Restrictions
Example: Creating an ARRAY Data Type
Example: Creating a Table with an ARRAY Column
Example: ARRAY Parameter in a Java UDF
Example: ARRAY Parameter in a Java External Stored Procedure
Related Information
ARRAY/VARRAY Functions and Operators
ARRAY/VARRAY Functions and Operators
ARRAY Element Reference
Usage Notes
Using ARRAY Element Reference with a SET Clause
Examples
Example: Showing the ARRAY Element Reference Syntax
Example: Creating Tables and Set Elements in a 1-D Array
Example: Creating Tables and Set Elements in a 3-D Array
Related Information
ARRAY_Constructor_Expression
ARRAY_Constructor_Expression Syntax
Argument Type and Rules
Usage Notes
Invoking the ARRAY Constructor Expression without the NEW Keyword
Example: Using an ARRAY Constructor Expression
ARRAY Scope Reference
Usage Notes
Rules
Example: Returning the Number of Distinct Elements within a Range
Related Information
ARRAY_AGG
ARRAY_AGG Syntax
Argument Types
Usage Notes
Result Value and Rules
Example: Inserting Data from a Source Table Into a New Table
UNNEST
UNNEST Syntax
Argument Types
Usage Notes
Example: Calling UNNEST
Related Information
CARDINALITY
CARDINALITY Syntax
Argument Type
Example: Return an Integer that Represents the Number of Elements in the 1-D ARRAY
Related Information
ARRAY_CONCATENATION_OPERATOR
ARRAY_CONCATENATION_OPERATOR Syntax
Argument Type
Example: Concatenating 1-D ARRAY values
ARRAY_CONCATENATION_FUNCTION
ARRAY_CONCATENATION_FUNCTION Syntax
Argument Type
Example: Concatenating Two 1-D ARRAY Values
ARRAY_COMPARISON_FUNCTION
ARRAY_COMPARISON_FUNCTION Syntax
Argument Type
Usage Notes
Supported ARRAY Comparison Functions
Restrictions
Examples
Example: Querying a 1-D ARRAY Data Type and Table
Example: Querying a 2-D ARRAY Data Type and Table
ARRAY_ARITHMETIC_FUNCTION
ARRAY_ARITHMETIC_FUNCTION Syntax
Argument Types
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY
Related Information
ARRAY_SUM
ARRAY_SUM Syntax
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY_SUM
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_SUM
Related Information
ARRAY_AVG
ARRAY_AVG Syntax
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY_AVG
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_AVG
ARRAY_MAX
ARRAY_MAX Syntax
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY_MAX
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_MAX
ARRAY_MIN
ARRAY_MIN Syntax
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY_MIN
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_MIN
Related Information
ARRAY_COUNT_DISTINCT
ARRAY_COUNT_DISTINCT Syntax
Usage Notes
Examples
Example: Querying a 1-D ARRAY Data Type and Table using ARRAY_COUNT_DISTINCT
Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_COUNT_DISTINCT
ARRAY_GET
ARRAY_GET Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using ARRAY_GET
Example: Query a 2-D ARRAY Data Type and Table using ARRAY_GET
ARRAY_COMPARE
ARRAY_COMPARE Syntax
Usage Notes
Restrictions
ARRAY_UPDATE
ARRAY_UPDATE Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using ARRAY_UPDATE
Example: Query a 2-D ARRAY Data Type and Table using ARRAY_UPDATE
Related Information
ARRAY_UPDATE_STRIDE
ARRAY_UPDATE_STRIDE Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using ARRAY_UPDATE_STRIDE
Example: Query a 2-D ARRAY Data Type and Table using ARRAY_UPDATE_STRIDE
Related Information
OEXISTS
OEXISTS Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OEXISTS
Example: Query a 2-D ARRAY Data Type and Table using OEXISTS
Related Information
OCOUNT
OCOUNT Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OCOUNT
Example: Query a 2-D ARRAY Data Type and Table using OCOUNT
Related Information
OLIMIT
OLIMIT Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OLIMIT
Example: Query a 2-D ARRAY Data Type and Table using OLIMIT
Related Information
OFIRST
OFIRST Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OFIRST
Example: Query a 2-D ARRAY Data Type and Table using OFIRST
Related Information
OLAST
OLAST Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OLAST
Example: Query a 2-D ARRAY Data Type and Table using OLAST
Related Information
OPRIOR
OPRIOR Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OPRIOR
Example: Query a 2-D ARRAY Data Type and Table using OPRIOR
Related Information
ONEXT
ONEXT Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using ONEXT
Example: Query a 2-D ARRAY Data Type and Table using ONEXT
Related Information
OEXTEND
OEXTEND Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OEXTEND
Example: Using OEXTEND to Fill the End of a Constructed ARRAY with NULL Elements
Example: Query a 2-D ARRAY Data Type and Table using OEXTEND
Related Information
OTRIM
OTRIM Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using OTRIM
Example: Query a 2-D ARRAY Data Type and Table using OTRIM
Related Information
ODELETE
ODELETE Syntax
Usage Notes
Examples
Example: Query a 1-D ARRAY Data Type and Table using ODELETE
Example: Query a 2-D ARRAY Data Type and Table using ODELETE
Related Information
UDT Data Type
UDT Data Type Syntax
Usage Notes
UDT Data Type Examples
Related Information
Parameter Data Types
TD_ANYTYPE Data Type
Usage Notes
Example: TD_ANYTYPE
Related Information
VARIANT_TYPE Data Type
Usage Notes
Example: VARIANT_TYPE
Related Information
Data Type Formats and Format Phrases
Data Type Default Formats
Default Formats
Changing the Default Formats
DATE Formats
Changing or Overriding the DATE Format Settings
The DateForm Setting
System Default DATE Format
Using the FORMAT Phrase to Set DATE Formats
Valid DATE Formats
Hierarchy of Date Formats
TIME and TIMESTAMP Formats
Changing or Overriding the TIME and TIMESTAMP Format Settings
System Default TIME and TIMESTAMP Format
Using the FORMAT Phrase to Set TIME or TIMESTAMP Formats
TIME and TIMESTAMP Format Components
Hierarchy of TIME and TIMESTAMP Formats
Example: TIME Output Format in Field Mode
Example: TIMESTAMP Output Format in Field Mode
FORMAT
Syntax
Usage Notes
Relationship to SDF
Example Display Formats
Examples
Example: Without FORMAT Clause
Example: With FORMAT Clause
Example: Using FORMAT to Change the Format of Returned Data
Example: Using FORMAT to Override Default Format
Example: Using FORMAT as Part of Derived Expression
Related Information
FORMAT Phrase and Character Formats
Default Result for Character Data
Formatting Characters
How FORMAT Applies to Server Character Sets
Truncation of KANJI1 Character Set Fields [Deprecated]
Examples
Example: FORMAT Phrase and Character Formats
Example: Formatting Characters
FORMAT Phrase and NUMERIC Formats
Formatting Characters for Non-Monetary Numeric Information
Formatting Characters for Monetary Numeric Information
Results of a FORMAT Phrase Defining Too Few Positions
Examples: Rounding
Examples: Display Results
FORMAT Phrase and DateTime Formats
Formatting Characters for Date Information
Formatting Characters for Time Information
Kanji Date and Time Markers
Examples: Date Formats
Examples: Time Formats
Examples: Timestamp Formats
Format Consistency
DATE Comparisons
Examples: Querying Using Date Formats
Examples: Using Time Formats
Record Mode Import Anomaly
FORMAT Phrase, DateTime Formats, and Japanese Character Sets
Separators
Kanji Date Markers
Kanji Time Markers
Using FORMAT for Input
Rules Applied for the First Year of an Era
Naming Columns and Expressions
Naming Columns
Rules for Columns Associated With an Explicit Table Name
Rules for Columns Not Associated With an Explicit Table Name
Qualifying Column Names and Named Objects
AS
Syntax
Usage Notes
Examples
Example: Using AS to Assign a Temporary Name to an Expression
Example: Creating a Temporary Named Column
NAMED
Syntax
Usage Notes
Differences Between NAMED and AS
Example: Using a NAMED Phrase
TITLE
Syntax
Usage Notes
Determining the Title for a Column or Expression
TITLE Phrase Rules
Examples
Example: Using the TITLE Phrase
Example: Using the TITLE Phrase in a SELECT Statement
Data Type Conversions
Data Type Conversions
Forms of Data Type Conversions
Implicit Type Conversions
Examples
Example: Implicit Type Conversion During Assignment
Example: Implicit Type Conversion During Comparison
Example: Implicit Type Conversion in Parameter Passing Operations
Supported Data Types
Implicit Conversion of DateTime Types
Implicit Conversion Rules
Truncation During Conversion
Restrictions
Related Information
CAST in Explicit Data Type Conversions
Usage Notes
Character Truncation Rules
Server Character Set Rules
Numeric Overflow, Field Mode, and CAST
Examples
Related Information
Data Conversions in Field Mode
Field Mode: User Response Data
Conversions to Numeric Types
Byte-to-Byte Conversion
Byte-to-Byte Conversion with CAST
Teradata Byte-to-Byte Conversion Syntax
Usage Notes
Conversions Where Source and Target Types Differ in Length
Supported Source and Target Data Types
Rules for Implicit Byte-to-UDT Conversions
Using HASHBUCKET to Convert a BYTE Type to an INTEGER Type
Examples
Example: Explicit Conversion of BLOB to VARBYTE
Example: Implicit Conversion of VARBYTE to BLOB
Character-to-Character Conversion
Character-to-Character Conversion with CAST
Teradata Character-to-Character Conversion
Usage Notes
General Usage Notes
CAST Syntax Usage Notes
Teradata Conversion Syntax Usage Notes
Implicit Character-to-Character Conversion
Related Information
Implicit Character-to-Character Translation
ANSI Compliance
Character Literals
KANJISJIS Server Character Set
SQL Rules for Implicit Translation for Expression and Function Arguments
Character-to-DATE Conversion
Character-to-DATE Conversion with CAST
Teradata Character-to-DATE Conversion Syntax
Usage Notes
Character String Format
Forcing a FORMAT on CAST for Converting Character to DATE
Character Strings That Omit Day, Month, or Year
Implicit Character-to-DATE Conversion
Examples
Example: IntegerDate Dateform Mode
Example: ANSIDate Dateform Mode
Example: Implicit Character-to-DATE Conversion
Related Information
Character-to-INTERVAL Conversion
Character-to-INTERVAL Conversion with CAST
Teradata Character-to-INTERVAL Conversion Syntax
Usage Notes
Examples
Example: Querying with CAST
Example: Converting to an INTERVAL Value
Character-to-Numeric Conversion
Character-to-Numeric Conversion with CAST
Teradata Character-to-Numeric Conversion Syntax
Usage Notes
Supported Character Types
Numeric Overflow
FORMAT Phrase Controls Parsing of the Data
Implicit Character-to-Numeric Conversion
Example: Implicit Conversion of Character to Numeric
Related Information
Character-to-Period Conversion
Usage Notes
Character Strings that Use ANSI DateTime Format
Implicit Character-to-Period Conversion
Example: Casting Concatenated Character Literals
Related Information
Character-to-TIME Conversion
Character-to-TIME Conversion with CAST
Teradata Character-to-TIME Conversion Syntax
Usage Notes
Conversions That Include Time Zone
Conversions That Include Fractional Seconds
Character Strings That Omit Hour, Minute, or Second
FORMAT Phrase Restrictions
Implicit Character-to-TIME Conversion
Examples
Example: Fractional Seconds
Example: Truncation of Non-pad Character Data
Example: Non Valid MINUTE Value
Example: FORMAT Phrase
Example: Implicit Conversion of Character to TIME
Related Information
Character-to-TIMESTAMP Conversion
Character-to-TIMESTAMP Conversion with CAST
Teradata Character-to-TIMESTAMP Conversion Syntax
Usage Notes
Conversions That Include Time Zone
Conversions That Include Fractional Seconds
Character Strings That Omit Day, Month, Year, Hour, Minute, or Second
Restrictions on FORMAT Phrase
Implicit Character-to-TIMESTAMP Conversion
Example: Querying with CAST
Related Information
Character-to-UDT Conversion
Usage Notes
Implicit Character-to-UDT Conversion
Related Information
Character Data Type Assignment Rules
DATE-to-Character Conversion
DATE-to-Character Conversion with CAST
Teradata DATE-to-Character Conversion Syntax
Usage Notes
Restrictions
Forcing a FORMAT on CAST for Converting DATE to Character
Examples
Example: Converting a DATE Value to a Character String
Example: Creating a Script to Convert Date Values
DATE-to-DATE Conversion
DATE-to-DATE Conversion with CAST
Teradata DATE-to-DATE Conversion Syntax
Example: Finding Employee Birthdates
DATE-to-Numeric Conversion
DATE-to-Numeric Conversion with CAST
Teradata DATE-to-Numeric Conversion Syntax
Usage Notes
FORMAT Phrase
Implicit DATE-to-Numeric Conversion
Example: DATE-to-Numeric Conversion
DATE-to-Period Conversion
Usage Notes
Examples
Example: Casting a DATE literal as PERIOD(DATE)
Example: Casting a DATE literal as PERIOD(TIMESTAMP(4))
DATE-to-TIMESTAMP Conversion
DATE-to-TIMESTAMP Conversion with CAST
Teradata DATE-to-TIMESTAMP Conversion Syntax
Usage Notes
Implicit DATE-to-TIMESTAMP Conversion
Examples
Example: Converting a DATE Value to a TIMESTAMP Value
Example: Converting a DATE Value to a TIMESTAMP WITH TIME ZONE value
Example: CAST for a TIMESTAMP
Example: Converting the DATE Value to a TIMESTAMP Value Based On a Time Zone String
DATE-to-UDT Conversion
Usage Notes
Implicit DATE-to-UDT Conversion
Related Information
INTERVAL-to-Character Conversion
INTERVAL-to-Character Conversion with CAST
Usage Notes
INTERVAL-to-Fixed CHARACTER Conversion
INTERVAL-to-VARCHAR Conversion
Teradata INTERVAL-to-Character Conversion Syntax
INTERVAL-to-INTERVAL Conversion
INTERVAL-to-INTERVAL Conversion with CAST
Teradata INTERVAL-to-INTERVAL Conversion Syntax
Usage Notes
Compatible Types
Precision of Source and Target Types
Implicit INTERVAL-to-INTERVAL Conversion
Examples
Example: Least Significant Field in Source Lower Than Target
Example: Least Significant Field in Source Lower Than Target
Example: Least Significant Field in Source Higher Than Target
Example: Least Significant Field in Source Higher Than Target
Example: Most Significant Field in Source Higher Than Target
Example: Implicit Type Conversion During Assignment
INTERVAL-to-Numeric Conversion
INTERVAL-to-Numeric Conversion with CAST
Teradata INTERVAL-to-Numeric Conversion Syntax
Implicit INTERVAL-to-Numeric Conversion
Example: Using CAST to Convert INTERVAL MONTH Values
INTERVAL-to-UDT Conversion
Usage Notes
Implicit INTERVAL-to-UDT Conversion
Related Information
Numeric-to-Character Conversion
Numeric-to-Character Conversion with CAST
Teradata Numeric-to-Character Conversion Syntax
Usage Notes
How CAST Differs from Teradata Conversion Syntax
Supported Character Types
Implicit Numeric-to-Character Conversion
Examples
Example: Converting an INTEGER Data Type to a Character
Example: Converting Salaries
Example: Converting Employee Numbers
Related Information
Numeric-to-DATE Conversion
Numeric-to-DATE Conversion with CAST
Teradata Numeric-to-DATE Conversion Syntax
Usage Notes
Translation of Numbers to Dates
Range of Allowable Values
Numeric-to-DATE Implicit Type Conversion
Example: Converting a Numeric Integer Expression to a Date Format
Related Information
Numeric-to-INTERVAL Conversion
Numeric-to-INTERVAL Conversion with CAST
Teradata Numeric-to-INTERVAL Conversion Syntax
Usage Notes
Implicit Numeric-to-INTERVAL Conversion
Example: Converting Numeric Data to an INTERVAL Value
Numeric-to-Numeric Conversion
Numeric-to-Numeric Conversion with CAST
Teradata Numeric-to-Numeric Conversion Syntax
Usage Notes
Conversion to FLOAT/REAL/DOUBLE PRECISION
Truncation and Rounding During Conversion
Using CAST in Applications With DECIMAL Type Size Restrictions
Using CAST To Avoid Numeric Overflow
Implicit Numeric-to-Numeric Conversion
Examples
Example: Casting a Numeric Integer Expression
Example: Changing the FORMAT Phrase to Display a Numeric Value
Related Information
Numeric-to-UDT Conversion
Usage Notes
Implicit Numeric-to-UDT Conversion
Period-to-Character Conversion
Period-to-Character Conversion with CAST
Teradata Period-to-Character Conversion Syntax
Usage Notes
Example: Converting a PERIOD Data Type to Its Canonical Character String Representation
Period-to-DATE Conversion
Usage Notes
Example: Converting Period Data to a DATE Value
Period-to-Period Conversion
Usage Notes
Compatible Types
PERIOD(DATE) to PERIOD(TIMESTAMP)
PERIOD(TIME) to PERIOD(TIME)
PERIOD(TIME) to PERIOD(TIMESTAMP)
PERIOD(TIMESTAMP) to PERIOD(DATE)
PERIOD(TIMESTAMP) to PERIOD(TIME)
PERIOD(TIMESTAMP) to PERIOD(TIMESTAMP)
Examples
Example: PERIOD(DATE) to PERIOD(TIMESTAMP)
Example: Least Significant Field in Source Lower Than Target
Period-to-TIME Conversion
Usage Notes
Example
Period-to-TIMESTAMP Conversion
Usage Notes
Example
Signed Zone DECIMAL Conversion
Using FORMAT in CREATE TABLE
Using Another FORMAT in the SELECT Statement
If FORMAT is Not Attached to the Column
Examples
Example
Example
Example
Example
Example
Related Information
TIME-to-Character Conversion
TIME-to-Character Conversion with CAST
Teradata TIME-to-Character Conversion Syntax
Usage Notes
Forcing a FORMAT on CAST for Converting TIME to Character
Example
TIME-to-Period Conversion
Usage Notes
Example
TIME-to-TIME Conversion
TIME-to-TIME Conversion with CAST
Teradata TIME-to-TIME Conversion Syntax
Usage Notes
Examples
Example
Example
Example
Example
Example
Example
Example
Related Information
TIME-to-TIMESTAMP Conversion
TIME-to-TIMESTAMP Conversion with CAST
Teradata TIME-to-TIMESTAMP Conversion Syntax
Usage Notes
Implicit TIME-to-TIMESTAMP Conversion
Examples
Example
Example
Example
Example
Example
Example
Example
Example
Example
Example
Example
Example
Example
Example
TIME-to-UDT Conversion
Implicit TIME-to-UDT Conversion
Usage Notes
TIMESTAMP-to-Character Conversion
TIMESTAMP-to-Character Conversion with CAST
Teradata TIMESTAMP-to-Character Conversion Syntax
Usage Notes
Forcing a FORMAT on CAST for Converting TIMESTAMP to Character
Example
TIMESTAMP-to-DATE Conversion
TIMESTAMP-to-DATE Conversion with CAST
Teradata TIMESTAMP-to-DATE Conversion Syntax
Usage Notes
Implicit TIMESTAMP-to-DATE Conversion
Examples
Example
Example
Example
Example
Example
Example
Example
TIMESTAMP-to-Period Conversion
Usage Notes
Example
TIMESTAMP-to-TIME Conversion
TIMESTAMP-to-TIME Conversion with CAST
Teradata TIMESTAMP-to-TIME Conversion Syntax
Usage Notes
Implicit TIMESTAMP-to-TIME Conversion
Examples
Example
Example
Example
Example
Example
Example
Example
TIMESTAMP-to-TIMESTAMP Conversion
TIMESTAMP-to-TIMESTAMP Conversion with CAST
Teradata TIMESTAMP-to-TIMESTAMP Conversion Syntax
Usage Notes
Examples
Example 1
Example 2
Example 3
Example 4
Example 5
Example 6
Example 7
Example 8
Related Information
TIMESTAMP-to-UDT Conversion
Usage Notes
Implicit TIMESTAMP-to-UDT Conversion
Related Information
TRYCAST
Examples
Example: TRYCAST Conversion Success
Example: TRYCAST Conversion Failure
UDT-to-Byte Conversion
UDT-to-Byte Conversion with CAST
Teradata UDT-to-Byte Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-Character Conversion
UDT-to-Character Conversion with CAST
Teradata UDT-to-Character Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-DATE Conversion
UDT-to-DATE Conversion with CAST
Teradata UDT-to-DATE Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-INTERVAL Conversion
UDT-to-INTERVAL Conversion with CAST
Teradata UDT-to-INTERVAL Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-Numeric Conversion
UDT-to-Numeric Conversion with CAST
Teradata UDT-to-Numeric Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-TIME Conversion
UDT-to-TIME Conversion with CAST
Teradata UDT-to-TIME Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-TIMESTAMP Conversion
UDT-to-TIMESTAMP Conversion with CAST
Teradata UDT-to-TIMESTAMP Conversion Syntax
Usage Notes
Implicit Type Conversion
Example
Related Information
UDT-to-UDT Conversion
Usage Notes
Implicit Type Conversion
Example
Related Information
Data Type Conversion Functions
TO_BYTES
Argument Types and Rules
Examples
Example: Decoding a Sequence of Characters to Base16
Example: Decoding a Sequence of Characters to Base36
Related Information
FROM_BYTES
Argument Types and Rules
Examples
Related Information
TO_NUMBER
Argument Types and Rules
format_arg Format Elements
Usage Notes
Examples
Example: Converting string_expr to a NUMBER Data Type
Example: Converting a Dollar Amount to a NUMBER Data Type
Related Information
TO_CHAR(Numeric)
Argument Types and Rules
Example: Converting a numeric_expr to a Character String
TO_CHAR(DateTime)
Argument Types and Rules
Example: Converting a date_timestamp_value to a Character String
Example: Truncating Digits in Fractional Seconds
TO_DATE
Argument Types and Rules
format_arg Format Elements
Example: Converting a string_expr to a DATE Data Type
Related Information
TO_TIMESTAMP
Argument Types and Rules
Examples
Example: Converting string_expr or integer_expr to a TIMESTAMP Data Type
Example: Converting a POSIX Epoch
Related Information
TO_TIMESTAMP_TZ
Argument Types and Rules
Example: Converting string_expr to a TIMESTAMP WITH TIME ZONE Data Type
Related Information
TO_YMINTERVAL
Argument Types and Rules
string_value Formats
Examples
Related Information
TO_DSINTERVAL
Argument Types and Rules
string_value Formats
Examples
Related Information
NUMTODSINTERVAL
Argument Types and Rules
Examples
NUMTOYMINTERVAL
Argument Types and Rules
Examples
Example: Converting numeric_value into an INTERVAL YEAR(40) TO MONTH Value
Example: Converting numeric_value into an INTERVAL YEAR(100) TO MONTH Value
Default Value Control Phrases
Using Default Value Control Phrases
NOT NULL Phrase
DEFAULT Phrase
Usage Notes
Examples
WITH DEFAULT Phrase
Usage Notes
Examples
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
External Representations for UDTs
Transforms Off and Period Data Type Support
UDTTransformsOff Flag
PeriodStructOn Flag
ArrayTransformsOff Flag
Flag Setting Combinations
Examples: Flag Setting Combinations
USING Clause
Transform Input/Output Strings for ARRAY/VARRAY UDTs
Additional Information
Teradata Links
You cannot pass ARRAY values as arguments to the search condition in a WHERE clause. You can pass individual elements of an ARRAY to the search condition in a WHERE clause. Also, you may use system functions provided by Vantage to do relational comparison on ARRAY data.
You cannot specify an ARRAY column in any of the following SQL DML clauses:INTERSECT, MINUS, or UNION set operator
DISTINCT operator
ORDER BY, GROUP BY, or HAVING clause of a SELECT statement
You cannot create UDMs for an ARRAY type. The only valid methods for an ARRAY type are the methods that Vantage creates automatically for an ARRAY type. Therefore, you cannot specify an ARRAY type name in the FOR clause of a CREATE/REPLACE METHOD statement.
An ARRAY column cannot be a component of an index.
Any restrictions that apply to a UDT also apply to an ARRAY type.