Identifies a field as a DATE value and simplifies handling and formatting of date variables.
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|
|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:
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.
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
You can specify dates in SQL statements in three ways:
- 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.
|Date string - LATIN character set||Enclosed in apostrophes.||'Jan 28, 1960'|
|Date string - Unicode hexadecimal format||Enclosed in apostrophes and followed by xc.||' 79797979C7
|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 the Utilities book.
|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|
|An abbreviated month name that matches one of the names specified by ShortMonths in the current Specification for Data Formatting (SDF) file.||Jan|
|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|
|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|
|An abbreviated day of the week name that matches one of the names specified by ShortDays in the current SDF.||Fri|
|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 SQL Functions, Operators, Expressions, and Predicates.
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 …|
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 SQL Functions, Operators, Expressions, and Predicates.
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 SQL Functions, Operators, Expressions, and Predicates.
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.
- Format conversion
- ADD_MONTHS function
- EXTRACT function
For more information on arithmetic operations on DATE types, see SQL Functions, Operators, Expressions, and Predicates.
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 SQL Functions, Operators, Expressions, and Predicates.
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 Data Dictionary.
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
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 …|
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