17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

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

Internal Representation of DATE

Internally, SQL Engine stores each DATE value as a four-byte signed integer using the following formula:

(YEAR - 1900) * 10000 + (MONTH * 100) + DAY

where the YEAR, MONTH, and DAY components, defined appropriately for the Gregorian calendar, have the following range of values.

Component   Range of Values  
Minimum Maximum
YEAR 1 9999
MONTH 1 12
DAY 1 28, 29, 30, or 31

(depending on the month and year)

External Representation of DATE

The external DATE format for the client depends on whether the DateForm option is set to IntegerDate or ANSIDate mode.

IF the DateForm is … THEN …
ANSIDate the export data type is CHARACTER(10) in the client character set with format 'YYYY-MM-DD'.
IntegerDate the export data characteristics are as follows:
  • Client CPU Architecture: IBM mainframe

    Client Internal Data Format: Four byte 32-bit signed 2’s complement integer, most significant byte first.

  • Client CPU Architecture: UTS, RISC, Motorola 68000, WE 32000

    Client Internal Data Format: Four byte 32-bit signed 2’s complement integer, most significant byte first.

  • Client CPU Architecture: Intel

    Client Internal Data Format: Four byte 32-bit signed 2’s complement integer, least significant byte first.

Determining the application definitions and client data types is the responsibility of the application programmer.

For an overview of the operations that set the DateForm option, see Hierarchy of Date Formats.

DATE Formats

To simplify your work with dates, Vantage assumes that you want dates in a preset format. Your database administrator sets that format for the system with the DateForm setting and the default date format setting in the specification for data formatting (SDF) file.

Those settings determine the following:
  • Export data type of DATE values
  • Data entry format for DATE values
  • Display format of DATE values in macros being executed
  • Date format for string-to-DATE comparisons and conversions
  • Display format of DATE columns in newly created tables and views
You can change or override the DATE format settings in any of the following ways:
  • Change the DateForm at the system level
  • Set the DateForm at the user or session level
  • Change the system default date format in the SDF
  • Specify a format in a FORMAT phrase

For more information about the DATE formats and how to change them, see Data Type Default Formats and DATE Formats.

Entering Dates

You can specify dates in SQL statements in three ways:
  • String
  • Number
  • ANSI date literal

The preferred way to specify dates is as an ANSI date literal. A valid ANSI date literal requires no additional format validation for SQL date operations. For example, the following SQL statement works regardless of the DATE format of the referenced column:

INSERT INTO DATETAB VALUES (DATE '2001-12-20');

Specifying the date as just a string requires matching the DATE format of the referenced column. SQL requires that date string comparisons have the same format. For example, the following statement would result in an error if the DATE format of the column DOB were 'YYYY-MM-DD':

SELECT Name FROM Employee WHERE DOB = 'Jan 31 1948';

For more information on the format characters that make up a valid DATE format, see Formatting Characters for Date Information.

You can also specify a date as a number. A date as a number requires no format checking, but entering the number for years outside of the 1900’s is not obvious and error prone. See the subsequent section about numeric date validation in the subsequent section.

The following is an example of 2001-12-20 entered as a number.

INSERT INTO DATETAB VALUES (1011220);

String Date Validation

When converting a string into a date value, Vantage validates the components of the string as described in the following table.

Component Requirement Example
Date string - LATIN character set Enclosed in apostrophes. 'Jan 28, 1960'
Date string - Unicode hexadecimal format Enclosed in apostrophes and followed by xc. ' 79797979C7

AF6D6DB7EE

6464C6FC 'xc

Separator Any non-numeric character can serve as a separator. Separators within a date do not have to match. /
YY The year as two or four numeric characters that are valid for the calendar. To support the century change transition, Vantage accepts four-digit years in two-digit year date formats.
The Century Break feature determines the century of a two-digit year. For more information, see Teradata Vantage™ - Database Utilities, B035-1102.
05

2003

YYYY

Y4

The year as four numeric characters that are valid for the calendar. 2003
MM The month as two numeric characters that are valid for the calendar. 02
MMM

M3

An abbreviated month name that matches one of the names specified by ShortMonths in the current Specification for Data Formatting (SDF) file. Jan
MMMM

M4

A full month name that matches one of the names specified by LongMonths in the current SDF. January
DD The day of the month as two numeric characters that are valid for the calendar. 03
DDD

D3

The day of the year as three numeric characters that are valid for the calendar. If the date includes a month, the day must fall in that month. 056
EEE

E3

An abbreviated day of the week name that matches one of the names specified by ShortDays in the current SDF. Fri
EEEE

E4

A day of the week name that matches one of the names specified by LongDays in the current SDF. Friday

For more information on CHARACTER to DATE conversions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Numeric Date Validation

Although not recommended, you can enter dates as numbers in the database storage format. SQL Engine stores each DATE value as a four-byte integer using the following formula:

(year - 1900) * 10000 + (month * 100) + day

Allowable date values range from AD January 1, 0001 to AD December 31, 9999. For example, December 31, 1985 would be stored as the integer 851231; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330.

The Century Break feature does not affect numeric dates.

Vantage exports dates in this numeric format if the current DateForm setting is set to IntegerDate.

The following table demonstrates how numeric dates are interpreted when inserted into a column. Note the translation of the third date, which was probably intended to be 1990-12-01.

This raw date value … Translates to this value …
901201 1990-12-01
1001201 2000-12-01
19901201 3890-12-01

Notice that this formula best fits two-digit dates in the 1900’s. Because of the difficulty of using this format outside of the 1900’s, we recommend specifying dates as ANSI date literals instead.

For more information on NUMERIC to DATE conversions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Implicit and Explicit DATE Conversion

Vantage performs implicit conversion on DATE types for certain operations such as assignment and comparison. This includes conversion from DATE to TIMESTAMP types in some cases. You can also use CAST to explicitly convert DATE to TIMESTAMP or other types.

For more information about all the cases in which implicit conversion of DATE is performed, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Arithmetic Operations on DATE

A field that is declared as DATE can be operated on using addition and subtraction. The MIN, MAX, AVERAGE, and COUNT aggregate operators can also be used with DATE values.

You can perform arithmetic operations on DATE data. For example, a number of days can be added to or subtracted from a DATE field, or from the DATE built-in value. The result of such operations is a value that is also a DATE data type.

The number of days between two dates can be calculated by subtracting one DATE value from another DATE value. The result is an integer.

Vantage handles month-to-month, year-to-year, and leap year arithmetic automatically; it does not store invalid dates, such as 1998-02-30.

In the following column definition, DOB is assigned the DATE data type.

DOB DATE FORMAT 'YYYY-MM-DD' NOT NULL
The following operations can be performed on data defined as DATE.
  • Arithmetic (addition and subtraction)

    Addition and subtraction of numeric types such as SMALLINT or INTEGER is still permitted, though these are non-ANSI operations because DATE is not a numeric data type in ANSI.

    Such arithmetic operations are treated as if the numeric value had been typed as INTERVAL DAY.

    For example, DATE - 12 is equivalent to the ANSI expression DATE - INTERVAL ‘12’ DAY.

  • Comparison
    • =
    • <>
    • >
    • <
    • OVERLAPS
  • Format conversion
  • ADD_MONTHS function
  • EXTRACT function

For more information on arithmetic operations on DATE types, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Calendar functions and CALENDAR System View

Vantage provides a suite of calendar functions and the SYS_CALENDAR.CALENDAR view to support DateTime operations that use calendar attributes. For example, the td_day_of_month function returns the number of days from the beginning of the month to the specified date. The calendar functions provide better performance as compared to using the CALENDAR view to obtain similar results.

For more information about the calendar functions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

You can use the CALENDAR view to get attributes for any date between the years 1900 and 2100, such as which day of the week or which week of the month a date falls on.

You are encouraged to define views on the CALENDAR view because of its convenience. A useful view to define on CALENDAR is TODAY, defined as follows.

CREATE VIEW Today AS (
SELECT *
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date = DATE
);

CALENDAR permits easy specification of arithmetic expressions and aggregation. This is particularly useful in OLAP environments where it is common to request values aggregated by weeks, months, year-to-date, years, and so on.

For more information on the definition of the SYS_CALENDAR.CALENDAR system view, see Teradata Vantage™ - Data Dictionary, B035-1092.