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|
|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, 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.
- 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
- 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
- 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.
|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, 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.
|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 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 …|
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
- 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 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.