Teradata Date/Time Functions
Date/Time functions operate on either Date/Time or Interval values and provide a Date/Time value as a result.
The supported Date/Time functions are:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- EXTRACT
To avoid any synchronization problems, operations among these functions are guaranteed to use identical definitions for DATE, TIME, or TIMESTAMP, therefore following services are always valid:
- CURRENT_DATE = CURRENT_DATE
- CURRENT_TIME = CURRENT_TIME
- CURRENT_TIMESTAMP = CURRENT_TIMESTAMP
- CURRENT_DATE and CURRENT_TIMESTAMP always identify the same DATE
- CURRENT_TIME and CURRENT_TIMESTAMP always identify the same TIME
The values reflect the time when the request starts and does not change during the application’s duration.
Date Storage
Dates are stored as integer internally using the following formula.
To check how the dates are stored using the following query.
Since the dates are stored as an integer, we can perform some arithmetic operations on them.
Teradata supports most of the standards date functions. Some of the commonly used date functions are listed below, such as:
Date Function | Explanation |
---|---|
LAST_DAY | It returns the last day of the given month. It may contain the timestamp values as well. |
NEXT_DAY | It returns the date of the weekday that follows a particular date. |
MONTHS_BETWEEN | It returns the number of months between two date (timestamp) values. The result is always an integer value. |
ADD_MONTHS | It adds a month to the given date (timestamp) value and return resulting date value. |
OADD_MONTHS | It adds a month to the given date (timestamp) value and return resulting date value. |
TO_DATE | It converts a string value to a DATE value and returns the resulting date value. |
TO_TIMESTAMP | It converts a string value to a TIMESTAMP value and returns resulting timestamp value. |
TRUNC | It returns a DATE value with the time portion truncated to the unit specified by a format string. |
ROUND | It returns a DATE value with the time portion rounded to the unit specified by a format string. |
NUMTODSINTERVAL | It converts a numeric value to interval days to seconds. |
NUMTOYMINTERVAL | It converts a numeric value to interval years to the month. |
TO_DSINTERVAL | It converts a string value to interval days to second. |
TO_YMINTERVAL | It converts a string value to interval year to a month. |
EXTRACT | It extracts portions of the day, month, and year from a given date value. |
INTERVAL | INTERVAL function is used to perform arithmetic operations on DATE and TIME values. |
EXTRACT
EXTRACT function is used to extract portions of the day, month, and year from a DATE value. This function is also used to extract hour, minute, and second from TIME/TIMESTAMP value.
Examples
1. The following example shows how to extract Year value from Date and Timestamp values.
Output
2020
2. The following example shows how to extract Month values from Date and Timestamp values.
Output
3. The following example shows how to extract Day values from Date and Timestamp values.
Output
22
4. The following example shows how to extract Hour values from Date and Timestamp values.
Output
6
5. The following example shows how to extract Minute values from Date and Timestamp values.
Output
46
6. The following example shows how to extract the Second values from Date and Timestamp values.
Output
25.150000
INTERVAL
Teradata provides INTERVAL function to perform arithmetic operations on DATE and TIME values. There are two types of INTERVAL functions, such as:
1. Year-Month Interval
- YEAR
- YEAR TO MONTH
- MONTH
2. Day-Time Interval
- DAY
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE
- MINUTE TO SECOND
- SECOND
Examples
1. The following example adds 4 years to the current date.
Output
05/22/2024
2. The following example adds 4 years and 03 months to the current date.
Output
08/22/2024
3. The following example adds 03 days, 05 hours, and 10 minutes to the current timestamp.
Output
05-25-2020 10:07:25.150000+00.00