Commonly used Teradata Date Functions and Examples

  • Post author:
  • Post last modified:October 22, 2020
  • Post category:General
  • Reading time:5 mins read

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.

Teradata Date Functions

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.

Read:

Teradata Date Functions

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

Also Read:

Teradata Date Functions Examples

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
Tags: