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:

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