Teradata Extract Function Syntax and Example

The extract function in Teradata extracts date or time part of the date, time, timestamp, or interval fields. The extract function retrieves the sub part such as day, month, year, etc. This extract function is equivalent to date_part() function in other relational databases. In this article, we will check how to use an extract function to get specified subpart.

The extract function also converts the extracted value exact numeric format. The output is always a numeric value.

Teradata Extract Function

In a data warehouse, you will be working with many different data sources. Sometimes, you may get requirement to extract part of timestamp field such as date and pass it to calling application or retrieve and store only day into the data warehouse. The extract function will come to rescue in the situations like these.

For example, extract year and time part of the current datetime and append it to file name to create unique file name. Similarly, there are so many applications use extract function.

Teradata Extract Function Syntax

The extract function syntax is similar to the one present in any other database.

Following is the Teradata extract function syntax.

EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )

Different Subpart in Teradata Extract Function

The following table represents the descriptions of the different datepart or timepart used in extract function. These values are sometimes referred to as a units.

Datepart or Timepart	Descriptions
HOUR	Hours from the time or timestamp field.
MINUTE	Minutes from the time or timestamp field.
SECOND	Seconds from the time or timestamp field.
YEAR	Year
DAY	Day of the month
TIMEZONE_HOUR	Time zone hours
TIMEZONE_MINUTE	Time zone minutes

Teradata Date Extract Examples

The following examples demonstrates the usage of extract function.

Extract year from the current_timestamp in Teradata.

select extract(YEAR from current_timestamp) as "year";
       year
-----------
       2020

Extract month from the current_timestamp in Teradata.

select extract(MONTH from current_timestamp) as "month"
      month
-----------
         10

Get day from the current_timestamp in Teradata.

select extract(DAY from current_timestamp) as "day"
        day
-----------
         22

Extract hour from the current_timestamp in Teradata.

select extract(HOUR from current_timestamp) as "hour";
       hour
-----------
         13

Extract minute from the current_timestamp in Teradata.

select extract(MINUTE from current_timestamp) as "mins";
       mins
-----------
         41

Get seconds from the current_timestamp in Teradata.

select extract(SECOND from current_timestamp) as "secs";
      secs
----------
 50.070000

Extract Time zone minutes from the current_timestamp in Teradata.

select extract(TIMEZONE_MINUTE from current_timestamp) as "TZ_mins";
    TZ_mins
-----------
          0

Extract Time zone hours from the current_timestamp in Teradata.

select extract(TIMEZONE_hour from current_timestamp) as "TZ_hours";
   TZ_hours
-----------
          0