DROP TYPE Syntax Elements - Advanced SQL Engine - Teradata Database
SQL Data Types and Literals
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā¢
Introduction to SQL Data Types and Literals
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
Further Information
COMPRESS and DECOMPRESS Phrases
Syntax
ANSI Compliance
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 Topics
Constraint Attributes
Column and Table Constraints
Uniqueness Constraints
Definition
PRIMARY KEY Constraint
UNIQUE Constraint
CHECK Constraints
Usage Notes
Example: CHECK Constraints
Referential Constraints
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
FLOAT/REAL/DOUBLE PRECISION Data Types
NUMBER Data Type
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
VARCHAR Data Type
CLOB Data Type
Default Case Specificity of Character Columns
CASESPECIFIC Phrase
UPPERCASE Phrase
Teradata SQL Character Strings and Client Physical Bytes
CHARACTER SET Phrase
LATIN Server Character Set
UNICODE Server Character Set
GRAPHIC Server Character Set
KANJISJIS Server Character Set
KANJI1 Server Character Set
Byte and BLOB Data Types
Data Storage of Byte and BLOB Types
BYTE Data Type
VARBYTE Data Type
BLOB Data Type
LOB Functions
EMPTY_BLOB
ANSI Compliance
Argument Types
Result Type
Example
EMPTY_CLOB
ANSI Compliance
Argument Types
Result Type
Example
DateTime and Interval Data Types
DateTime Fields
Time Zones
Daylight Saving Time
DATE Data Type
TIME Data Type
TIMESTAMP Data Type
TIME WITH TIME ZONE Data Type
TIMESTAMP WITH TIME ZONE Data Type
INTERVAL YEAR Data Type
INTERVAL YEAR TO MONTH Data Type
INTERVAL MONTH Data Type
INTERVAL DAY Data Type
INTERVAL DAY TO HOUR Data Type
INTERVAL DAY TO MINUTE Data Type
INTERVAL DAY TO SECOND Data Type
INTERVAL HOUR Data Type
INTERVAL HOUR TO MINUTE Data Type
INTERVAL HOUR TO SECOND Data Type
INTERVAL MINUTE Data Type
INTERVAL MINUTE TO SECOND Data Type
INTERVAL SECOND Data Type
Period Data Types
Period Data Types: Basic Definitions
Period Type Input and Output Parameters
Related Topics
PERIOD(DATE) Data Type
PERIOD(TIME) Data Type
PERIOD(TIME WITH TIME ZONE) Data Type
PERIOD(TIMESTAMP) Data Type
PERIOD(TIMESTAMP WITH TIME ZONE) Data Type
ARRAY/VARRAY Data Type
Syntax
ANSI Compliance
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 Topics
ARRAY/VARRAY Functions and Operators
ARRAY/VARRAY Functions and Operators
ARRAY Element Reference
ANSI Compliance
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 Topics
ARRAY_Constructor_Expression
ANSI Compliance
Argument Type and Rules
Invoking the ARRAY Constructor Expression Without the NEW Keyword
Example: Using an ARRAY Constructor Expression
ARRAY Scope Reference
ANSI Compliance
Usage Notes
Rules
Example: Returning the Number of Distinct Elements within a Range
Related Topics
ARRAY_AGG
ANSI Compliance
Argument Types
Result Type
Result Value and Rules
Usage Notes
Example: Inserting Data from a Source Table Into a New Table
UNNEST
ANSI Compliance
Argument Types
Result Type and Value
Usage Notes
Example: Calling UNNEST
Related Topics
CARDINALITY
ANSI Compliance
Argument Type
Result Type and Value
Example: Return an Integer that Represents the Number of Elements in the 1-D ARRAY
Related Topics
ARRAY_CONCATENATION_OPERATOR
ANSI Compliance
Argument Type
Result Type and Value
Restrictions
Example: Concatenating 1-D ARRAY values
ARRAY_CONCATENATION_FUNCTION
ANSI Compliance
Argument Type
Result Type and Value
Restrictions
Example: Concatenating Two 1-D ARRAY Values
ARRAY_COMPARISON_FUNCTION
ANSI Compliance
Argument Type
Supported ARRAY Comparison Functions
Usage Notes
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
ANSI Compliance
Argument Types
Usage Notes
Result Type and Value
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 Topics
ARRAY_SUM
ANSI Compliance
Usage Notes
Result Type
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 Topics
ARRAY_AVG
ANSI Compliance
Usage Notes
Result Type
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
ANSI Compliance
Usage Notes
Result Type
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
ANSI Compliance
Usage Notes
Result Type
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 Topics
ARRAY_COUNT_DISTINCT
ANSI Compliance
Usage Notes
Result Type
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
ANSI Compliance
Usage Notes
Result Type
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
ANSI Compliance
Usage Notes
Restrictions
ARRAY_UPDATE
DROP TYPE Syntax Elements
array_expr
new_value
scope_reference
array_index
ANSI Compliance
Usage Notes
Result Type and Value
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 Topics
ARRAY_UPDATE_STRIDE
ANSI Compliance
Usage Notes
Result Type and Value
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 Topics
OEXISTS
ANSI Compliance
Usage Notes
Result Type and Value
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 Topics
OCOUNT
ANSI Compliance
Usage Notes
Result Type
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 Topics
OLIMIT
ANSI Compliance
Usage Notes
Result Type
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 Topics
OFIRST
ANSI Compliance
Usage Notes
Result Type
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 Topics
OLAST
ANSI Compliance
Usage Notes
Result Type
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 Topics
OPRIOR
ANSI Compliance
Usage Notes
Result Type
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 Topics
ONEXT
ANSI Compliance
Usage Notes
Result Type
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 Topics
OEXTEND
DROP TYPE Syntax Elements
TD_SYSFNLIB
array_expr
num_spaces
index_value
array_bound
ANSI Compliance
Usage Notes
Result Type
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 Topics
OTRIM
ANSI Compliance
Usage Notes
Result Type
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 Topics
ODELETE
ANSI Compliance
Usage Notes
Result Type
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 Topics
UDT Data Type
UDT Data Type Syntax
UDT Data Type Examples
Parameter Data Types
TD_ANYTYPE Data Type
VARIANT_TYPE Data Type
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
ANSI Compliance
Usage Notes
Relationship to SDF
Example Display Formats
Further Information
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
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
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
ANSI Compliance
Usage Notes
Examples
Example: Using AS to Assign a Temporary Name to an Expression
Example: Creating a Temporary Named Column
NAMED
Syntax
ANSI Compliance
Usage Notes
Differences Between NAMED and AS
Example: Using a NAMED Phrase
TITLE
Syntax
ANSI Compliance
Usage Notes
Determining the Title for a Column or Expression
Examples
Example: Using the TITLE Phrase
Example: Using the TITLE Phrase in a SELECT Statement
TITLE Phrase Rules
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 Topics
CAST in Explicit Data Type Conversions
ANSI Compliance
Usage Notes
Character Truncation Rules
Server Character Set Rules
Numeric Overflow, Field Mode, and CAST
Related Topics
Examples
Data Conversions in Field Mode
Field Mode: User Response Data
Conversions to Numeric Types
Byte Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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
ANSI Compliance
CAST Syntax Usage Notes
Teradata Conversion Syntax
ANSI Compliance
Teradata Conversion Syntax Usage Notes
Implicit Character-to-Character Conversion
General Usage Notes
Related Topics
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
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Character-to-DATE Conversion
Usage Notes
Character String Format
Forcing a FORMAT on CAST for Converting Character to DATE
Character Strings That Omit Day, Month, or Year
Examples
Example: IntegerDate Dateform Mode
Example: ANSIDate Dateform Mode
Example: Implicit Character-to-DATE Conversion
Related Topics
Character-to-INTERVAL Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Examples
Example: Querying with CAST
Example: Converting to an INTERVAL Value
Character-to-Numeric Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Character-to-Numeric Conversion
Supported Character Types
Usage Notes
Numeric Overflow
FORMAT Phrase Controls Parsing of the Data
Example: Implicit Conversion of Character to Numeric
Related Topics
Character-to-Period Conversion
ANSI Compliance
Usage Notes
Implicit Character-to-Period Conversion
Character Strings that Use ANSI DateTime Format
Example: Casting Concatenated Character Literals
Related Topics
Character-to-TIME Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Character-to-TIME Conversion
Usage Notes
Conversions That Include Time Zone
Conversions That Include Fractional Seconds
Character Strings That Omit Hour, Minute, or Second
FORMAT Phrase Restrictions
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 Topics
Character-to-TIMESTAMP Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Character-to-TIMESTAMP Conversion
Usage Notes
Example: Querying with CAST
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
Related Topics
Character-to-UDT Conversion
ANSI Compliance
Usage Notes
Implicit Character-to-UDT Conversion
Related Topics
Character Data Type Assignment Rules
DATE-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Example: Finding Employee Birthdates
DATE-to-Numeric Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
FORMAT Phrase
Implicit DATE-to-Numeric Conversion
Example: DATE-to-Numeric Conversion
DATE-to-Period Conversion
ANSI Compliance
Usage Notes
Examples
Example: Casting a DATE literal as PERIOD(DATE)
Example: Casting a DATE literal as PERIOD(TIMESTAMP(4))
DATE-to-TIMESTAMP Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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
ANSI Compliance
Usage Notes
Implicit DATE-to-UDT Conversion
Related Topics
INTERVAL-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
INTERVAL-to-Fixed CHARACTER Conversion
INTERVAL-to-VARCHAR Conversion
INTERVAL-to-INTERVAL Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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
ANSI Compliance
Implicit INTERVAL-to-NUMERIC Conversion
Teradata Conversion Syntax
ANSI Compliance
Implicit INTERVAL-to-NUMERIC Conversion
Example: Using CAST to Convert INTERVAL MONTH Values
INTERVAL-to-UDT Conversion
ANSI Compliance
Usage Notes
Implicit INTERVAL-to-UDT Conversion
Related Topics
Numeric-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Numeric-to-Character Conversion
How CAST Differs from Teradata Conversion Syntax
Supported Character Types
Usage Notes
Examples
Example: Converting an INTEGER Data Type to a Character
Example: Converting Salaries
Example: Converting Employee Numbers
Related Topics
Numeric-to-DATE Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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 Topics
Numeric-to-INTERVAL Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Numeric-to-INTERVAL Conversion
Example: Converting Numeric Data to an INTERVAL Value
Numeric-to-Numeric Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Implicit Numeric-to-Numeric Conversion
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
Examples
Example: Casting a Numeric Integer Expression
Example: Changing the FORMAT Phrase to Display a Numeric Value
Related Topics
Numeric-to-UDT Conversion
ANSI Compliance
Usage Notes
Implicit Numeric-to-UDT Conversion
Period-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
Usage Notes
Example: Converting a PERIOD Data Type to Its Canonical Character String Representation
Period-to-DATE Conversion
ANSI Compliance
Usage Notes
Example: Converting Period Data to a DATE Value
Period-to-Period Conversion
ANSI Compliance
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
ANSI Compliance
Usage Notes
Example
Period-to-TIMESTAMP Conversion
ANSI Compliance
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 Topics
TIME-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Forcing a FORMAT on CAST for Converting TIME to Character
Example
TIME-to-Period Conversion
ANSI Compliance
Usage Notes
Example
TIME-to-TIME Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Examples
Example
Example
Example
Example
Example
Example
Example
Related Topics
TIME-to-TIMESTAMP Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
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
ANSI Compliance
Usage Notes
Implicit TIME-to-UDT Conversion
TIMESTAMP-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Forcing a FORMAT on CAST for Converting TIMESTAMP to Character
Example
TIMESTAMP-to-DATE Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit TIMESTAMP-to-DATE Conversion
Examples
Example
Example
Example
Example
Example
Example
Example
TIMESTAMP-to-Period Conversion
ANSI Compliance
Usage Notes
Example
TIMESTAMP-to-TIME Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit TIMESTAMP-to-TIME Conversion
Examples
Example
Example
Example
Example
Example
Example
Example
TIMESTAMP-to-TIMESTAMP Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Examples
Example
Example
Example
Example
Example
Example
Example
Example
Related Topics
TIMESTAMP-to-UDT Conversion
ANSI Compliance
Usage Notes
Implicit TIMESTAMP-to-UDT Conversion
Related Topics
TRYCAST
Output
Examples
Example
Example: TRYCAST Conversion Failure
UDT-to-Byte Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-Character Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-DATE Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-INTERVAL Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-Numeric Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-TIME Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-TIMESTAMP Conversion
ANSI Compliance
Teradata Conversion Syntax
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
UDT-to-UDT Conversion
ANSI Compliance
Usage Notes
Implicit Type Conversion
Example
Related Topics
Data Type Conversion Functions
TO_BYTES
ANSI Compliance
Argument Types and Rules
Result Type
Examples
Example: Decoding a Sequence of Characters to Base16
Example: Decoding a Sequence of Characters to Base36
Related Topics
FROM_BYTES
ANSI Compliance
Argument Types and Rules
Result Type
Examples
Related Topics
TO_NUMBER
format_arg Format Elements
ANSI Compliance
Argument Types and Rules
Result Type
Usage Notes
Examples
Example: Converting string_expr to a NUMBER Data Type
Example: Converting a Dollar Amount to a NUMBER Data Type
Related Topics
TO_CHAR(Numeric)
ANSI Compliance
Argument Types and Rules
Result Type
Example: Converting a numeric_expr to a Character String
TO_CHAR(DateTime)
ANSI Compliance
Argument Types and Rules
Result Type
Example: Converting a date_timestamp_value to a Character String
TO_DATE
format_arg Format Elements
ANSI Compliance
Argument Types and Rules
Result Type
Example: Converting a string_expr to a DATE Data Type
Related Topics
TO_TIMESTAMP
ANSI Compliance
Argument Types and Rules
Result Type
Examples
Example: Converting string_expr or integer_expr to a TIMESTAMP Data Type
Example: Converting a POSIX Epoch
Related Topics
TO_TIMESTAMP_TZ
ANSI Compliance
Argument Types and Rules
Result Type
Example: Converting string_expr to a TIMESTAMP WITH TIME ZONE Data Type
Related Topics
TO_YMINTERVAL
ANSI Compliance
Argument Types and Rules
string_value Formats
Result Type
Examples
Related Topics
TO_DSINTERVAL
ANSI Compliance
Argument Types and Rules
string_value Formats
Result Type
Examples
Related Topics
NUMTODSINTERVAL
ANSI Compliance
Argument Types and Rules
Result Type
Examples
NUMTOYMINTERVAL
ANSI Compliance
Argument Types and Rules
Result Type
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
WITH DEFAULT Phrase
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
database_name
Name of the containing database for user_defined_type_name .
This is always SYSUDTLIB.
user_defined_type_name
Name of the user-defined type, whether ARRAY/VARRAY, distinct, or structured, whose definition is to be dropped from the dictionary.
Before you drop a UDT, you must ensure that you resolve any dependencies that exist on it. You can execute the system macro SYSUDTLIB.HelpDependencies to find any existing dependencies for a type you intend to drop.