DATE Data Type

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Purpose

Identifies a field as a DATE value and simplifies handling and formatting of date variables.

Syntax



attributes
Appropriate data type, column storage, or column constraint attributes.
See Core Data Type Attributes and Storage and Constraint Attributes for specific information.

ANSI Compliance

DATE with a DateForm of ANSIDate is ANSI SQL:2011 compliant.

DATE with a DateForm of IntegerDate is a Teradata extension to the ANSI SQL:2011 standard.

Internal Representation of DATE

Internally, Teradata Database 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, Teradata Database 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 details 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 details 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 "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, Teradata Database 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, Teradata Database 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 “Data Type Conversions” in Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.

Numeric Date Validation

Although not recommended, you can enter dates as numbers in the Teradata Database storage format. Teradata Database 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.

Teradata Database 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 “Data Type Conversions” in Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.

Implicit and Explicit DATE Conversion

Teradata Database 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 details about all the cases in which implicit conversion of DATE is performed, see “Data Type Conversions” in 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.

Teradata Database 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

Teradata Database 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 details on the definition of the SYS_CALENDAR.CALENDAR system view, see Teradata Vantage™ Data Dictionary, B035-1092.

Example: Queries Using Dates

To list all male employees currently over 50 years of age, the following statement might be entered:

SELECT name, dob
FROM employee
WHERE CURRENT_DATE > ADD_MONTHS (dob, 12*50)
AND sex = 'M' ;

The system returns:

Name                            DOB
---------                ----------
Russell S                1932-06-05
Carter J                 1935-03-12
Inglis C                 1938-03-07

Note that CURRENT_DATE is used in the expression to get the current date from the system.

To project a date three months from the date of birth of employee Russell, enter:

SELECT name, ADD_MONTHS (dob,3)
FROM employee
WHERE name = ’Russell S’ ;

The system returns:

Name                 ADD_MONTHS(dob,3)
---------            -----------------
Russell S            1932-09-05
Three months is not a specific number of days. In this particular case, “three months” is 92 days.

Example: Using an Integer to Represent a Date

To list employees who were born between March 7, 1938, and August 25, 1942, you could specify the date information as follows:

SELECT name, dob
FROM employee
WHERE dob BETWEEN 380307
AND DATE '1942-08-25'
ORDER BY dob ;

In this example, the first date (380307) is an integer representing yymmdd. The values of the dob column are converted to INTEGER to compare with the integer value. The second date is in the preferred DATE literal form. The result returns the date of birth information as specified for the Employee table:

Name                  DOB
----------    -----------
Inglis C      Mar 07 1938
Peterson J    Mar 27 1942

The display of DOB is controlled by the format for Personnel.Employee.DOB: FORMAT ' MMM DD YYYY '.

Example: Changing the Date Format

To change the date format displayed above to an alternate form, change the SELECT to:

SELECT name, dob (FORMAT '99-99-99')
FROM employee
WHERE dob BETWEEN 380307 AND DATE '1942-08-25'
ORDER BY dob ;

This format specification changes the display to the following:

Name                    DOB
----------         --------
Inglis C           38-03-07
Peterson J         42-03-27

Example: Changing From Date Format To Integer Format

To change the display from date format to integer format, change the statement in Example 3 to:

SELECT name, dob (INTEGER)
FROM employee
WHERE dob BETWEEN 380307 AND 420825
ORDER BY dob ;

This format specification changes the display to the following:

Name                       DOB
----------         -----------
Inglis C                380307
Peterson J              420327

Further examples illustrating arithmetic operations on DATE appear in the following table. Assume the system date is Jan 24, 2001.

For this statement … The system returns this value …
SELECT CURRENT_DATE;
    Date
--------
01/01/24
SELECT CURRENT_DATE +3;
(Date+3)
--------
01/01/27
SELECT CURRENT_DATE -3;
(Date-3)
--------
01/01/21
SELECT CURRENT_DATE - CURRENT_DATE;
(Date-Date)
-----------
          0