Table of Contents
Ispirer Website
Ispirer Capabilities - Teradata Migration
Free Trial
Teradata Data Types
This article describes the data types supported by Teradata 13 and earlier.
Overview
| Data Type | Parameters and Range |
|---|---|
| Character Data Types | |
| CHAR(n) - Fixed-length character data | 1 ⇐ n ⇐ 64000/charsize, default is 1 |
| Date and Time Data Types | |
| TIMESTAMP(p) - Date and time data (day, month, year, hours, minutes, seconds and microseconds) | 0 ⇐ p ⇐ 6, default is 6 January 01, 0001 00:00:00 to December 31, 9999 23:59:61.999999 |
Character Data Types
CHAR
| Teradata - CHAR | |
|---|---|
| Syntax | CHAR[(n)] |
| Data | Fixed-length character data, single-byte and multibyte character sets |
| Parameters | n is the maximum number of characters if the CHARACTER SET is LATIN, UNICODE or GRAPHIC or bytes if the CHARACTER SET is KANJI1 or KANJISJIS, optional |
| Range | 1 ⇐ n ⇐ 64000 for LATIN and KANJI1 character sets, 1 ⇐ n ⇐ 32000 for UNICODE, GRAPHIC and KANJISJIS character sets |
| Default | n is 1 |
| Padding | Right-padded with spaces to n |
| Storage Size | n bytes |
| Synonyms | CHARACTER |
| Standards | ANSI SQL |
Teradata CHAR - Equivalents in Other Databases
| Database | Data Type and Conversion |
|---|---|
| Oracle | CHAR(n), 1 ⇐ n ⇐ 2000/charsize; NCHAR(n), 1 ⇐ n ⇐ 2000/charsize, Unicode - UTF-8 or UTF-16 |
| SQL Server | CHAR(n), 1 ⇐ n ⇐ 8000; NCHAR(n), 1 ⇐ n ⇐ 4000, Unicode - UCS-2 |
| IBM DB2 | CHAR(n), 1 ⇐ n ⇐ 254; GRAPHIC(n), 1 ⇐ n ⇐ 127, double-byte |
| MySQL | CHAR(n), 0 ⇐ n ⇐ 255; NCHAR(n), 0 ⇐ n ⇐ 255, Unicode - UTF-8 |
| PostgreSQL | CHAR(n), 1 ⇐ n ⇐ 1G |
| Sybase ASE | CHAR(n), 1 ⇐ n ⇐ pagesize (2K, 4K, 8K or 16K); NCHAR(n), 1 ⇐ n ⇐ pagesize; UNICHAR(n), 1 ⇐ n ⇐ pagesize, Unicode |
| Sybase ASA | CHAR(n), 1 ⇐ n ⇐ 32767/charsize; NCHAR(n), 1 ⇐ n ⇐ 8191, Unicode - UTF-8 |
| Informix DS | CHAR(n), 1 ⇐ n ⇐ 32767; NCHAR(n), 1 ⇐ n ⇐ 32767, database character set |
| HP Neoview | CHAR(n), 1 ⇐ n ⇐ 4036; NCHAR(n), 1 ⇐ n ⇐ 4036, Unicode - UCS-2 or ISO88591 (Latin1) |
| Interbase/Firebird | CHAR(n), 1 ⇐ n ⇐ 32767/charsize; NCHAR(n), 1 ⇐ n ⇐ 32767, single-byte ISO8859_1 (Latin 1); |
| Ingres | CHAR(n), 1 ⇐ n ⇐ 3200; NCHAR(n), 1 ⇐ n ⇐ 16000, Unicode - UTF-8 |
Date and Time Data Types
TIMESTAMP
| Teradata - TIMESTAMP | |
|---|---|
| Syntax | TIMESTAMP[(p)] [WITH TIME ZONE] |
| Data | Date and time data (day, month, year, hours, minutes, seconds and microseconds) |
| Parameters | p is the number of digits in the fractional part of second, 0 ⇐ p ⇐ 6; WITH TIME ZONE specifies that the time zone is stored along with the time values |
| Default | p is 6 |
| Range | January 01, 0001 00:00:00.000000 to December 31, 9999 23:59:61.999999 |
| Storage Size | 10 bytes (12 bytes when WITH TIME ZONE is specified) |
| Standards | ANSI SQL |
Teradata TIMESTAMP - Equivalents in Other Databases
| Database | Data Type and Conversion |
|---|---|
| Oracle | TIMESTAMP(p), January 1, 4712 BC 00:00:00.000000000 to December 31, 9999 23:59:59.999999999 (nanoseconds accuracy) |
| SQL Server | DATETIME, January 01, 1753 00:00:00.000 to December 31, 9999 23:59:59.997 with accuracy 1/300 second DATETIME2 and DATETIMEOFFSET, January 01, 0001 00:00:00.0000000 to December 31, 9999 23:59:59.9999999 (100 nanoseconds accuracy) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy) |
| MySQL | DATETIME, January 01, 1000 00:00:00 to December 31, 9999 23:59:59 (seconds accuracy) |
| PostgreSQL | TIMESTAMP(p), January 01, 4713 BC 00:00:00:000000 and December 31, 5874897 23:59:59:999999 (microseconds accuracy) |
| Sybase ASE | DATETIME, January 01, 1753 12:00:00.000 AM to December 31, 9999 11:59:59.999 PM (with accuracy 1/300 second) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy) |
| Informix | DATETIME, January 01, 0001 12:00:00.00000 AM to December 31, 9999 11:59:59.99999 PM (with 10 microseconds accuracy |
| Ingres | TIMESTAMP(p), 0001-01-01 00:00:00 to 9999-12-31 24:00:00 |
All Data Types