Teradata ANSI DateTime data types include:
• DATE
• TIME
• TIME WITH TIME ZONE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
The supported DateTime functions are:
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• EXTRACT
Teradata ADD_MONTHS function:
Example 1
This statement returns the current date plus 13 years.
SELECT ADD_MONTHS (CURRENT_DATE, 12*13);
Example 2
This statement returns the date 6 months ago.
SELECT ADD_MONTHS (CURRENT_DATE, -6);
Example 3
This statement returns the current TIMESTAMP plus four months.
SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 4);
Example 4
This statement returns the TIMESTAMP nine months from January 1, 1999. Note the literal form, which includes the keyword TIMESTAMP.
SELECT ADD_MONTHS (TIMESTAMP '1999-01-01 23:59:59', 9);
Example 5
This statement adds one month to January 30, 1999.
SELECT ADD_MONTHS ('1999-01-30', 1);
The result is 1999-02-28.
Example 6
The result of the SELECT statement in this example is a date in February, 1996. The result would be February 31, 1996 if that were a valid date, but because February 31 is not a valid date, ADD_MONTHS normalizes the answer. That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.
SELECT ADD_MONTHS ('1995-12-31', 2);
The result of this example is 1996-02-29. Note that 1996 was a leap year. If the interval were 14 months rather than 2, the result wouldbe '1997-02-28'.
Example 7
This statement performs the converse of the ADD_MONTHS function in “Example 5”. You might expect it to return ‘1999-01-30’, which is the source date in that example, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , -1);
ADD_MONTHS returns the result 1999-01-28. The function performs as designed and this result is not an error, though it might not be what you would expect from reading “Example 5.”
Example 8
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , 1);
ADD_MONTHS returns the result 1999-03-28.
Example 9
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-04-30' , -1);
ADD_MONTHS returns the result 1999-03-30.
Example 10
You might expect the following statement to return '1999-05-31', but it does not.
SELECT ADD_MONTHS ('1999-04-30' , 1);
ADD_MONTHS returns the result 1999-05-30.
Teradata EXTRACT function:
Example 1
The following example returns the year, as an integer, from the current date.
SELECT EXTRACT (YEAR FROM CURRENT_DATE);
Example 2
Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.
SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;
Example 3
The following returns 12 as an integer.
SELECT EXTRACT (DAY FROM '1996-12-12');
Example 4
This example returns an error because the character literal does not evaluate to a valid date.
SELECT EXTRACT (DAY FROM '1996-02-30');
Example 5
The following returns an error because the character string literal does not match the ANSI SQL:2008 date format.
SELECT EXTRACT (DAY FROM '96-02-15');
If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.
Example 6
The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.
SELECT EXTRACT (MINUTE FROM '23:59:17.3');
Example 7
This example returns the hour, as an integer, from the current time.
SELECT EXTRACT (HOUR FROM CURRENT_TIME);
Current time is retrieved as the system value TIME, to the indicated precision.
Example 8
The following example returns the seconds as DECIMAL(8,2). This is based on the fractional
seconds precision of 2 for CURRENT_TIME.
SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));
See more - SQL Functions, Operators, Expressions, and Predicates:
http://www.info.teradata.com/edownload.cfm?itemid=102320046