This article is about detailed descriptions and examples of the commonly used Teradata date functions that you can use to manipulate date columns in the Teradata, stored procedure or in embedded SQLs.
In the real word scenario, many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. Each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same.
Teradata supports most of standard date functions that all other RDBMS supports. Below are the some of the commonly used date functions in Teradata:
| Date Function | Description |
| LAST_DAY | Return last day of the given month. It may contain the timestamp values as well. |
| NEXT_DAY | Returns the date of the weekday that follows a particular date. |
| MONTHS_BETWEEN | Return number of months between two date (timestamp) values. The result is always an integer values. |
| ADD_MONTHS | Adds a month to the given date (timestamp) value and return resulting date value. |
| OADD_MONTHS | Adds a month to the given date (timestamp) value and return resulting date value. |
| TO_DATE | Convert a string value to a DATE value and return resulting date value |
| TO_TIMESTAMP | Convert a string value to a TIMESTAMP value and return resulting timestamp value |
| TRUNC | Returns a DATE value with the time portion truncated to the unit specified by a format string. |
| ROUND | Returns a DATE value with the time portion rounded to the unit specified by a format string. |
| NUMTODSINTERVAL | Convert numeric value to interval days to seconds |
| NUMTOYMINTERVAL | Convert numeric value to interval years to month |
| TO_DSINTERVAL | Convert string value to interval days to second |
| TO_YMINTERVAL | Convert string value to interval year to month |
| EXTRACT | Function extracts portions of day, month and year from a given date value |
| INTERVAL | INTERVAL function is used to perform arithmetic operations on DATE and TIME values |
Teradata get current date and timestamp examples
select current_date; Date 2017-10-25
select current_timestamp; Current TimeStamp(6) 2017-10-25 12:20:03.94
Example to add one month to current date in Teradata
select ADD_MONTHS(current_date,1); ADD_MONTHS(Date, 1) 2017-11-25
select current_date + INTERVAL '1' MONTH; (Date+ 1) 2017-11-25
Teradata Add one hour to current timestamp
select current_timestamp + INTERVAL '1' hour; (Current TimeStamp(6)+ 1) 2017-10-25 13:28:20.75
Teradata subtract one month from current date
select ADD_MONTHS(current_date,-1); ADD_MONTHS(Date, -1) 2017-09-25
select current_date - INTERVAL '1' MONTH; (Date- 1) 2017-09-25
Example to Extract day, month, year from current date in Teradata
select EXTRACT(DAY FROM current_date); EXTRACT(DAY FROM Date) 25
select EXTRACT(MONTH FROM current_date); EXTRACT(MONTH FROM Date) 10
select EXTRACT(YEAR FROM current_date); EXTRACT(YEAR FROM Date) 2017
Teradata get months between two date values
SELECT MONTHS_BETWEEN(DATE'2017-03-01', DATE'2017-01-01'); MONTHS_BETWEEN(2017-03-01,2017-01-01) 2
Teradata round up date to first day of the week
SELECT TRUNC(CAST('2017/01/05' AS DATE), 'D'); TRUNC('2017/01/05','D') 2017-01-01
