Extract function in Teradata with examples

Extract function in Teradata

EXTRACT function is used to get the Year,Month or Day from a date. Also it is helpful to extract the Hour, Minute, Second, Timezone_Hour, Timezone_Minute from the time value. EXTRACT function returns an exact numeric value for ANSI SQL:2011 Date Time values. The input values should given in the below ANSI format.

  • Date format – the string must match the ‘YYYY-MM-DD’ format.
  • Time format – the string must match the ‘HH:MI:SS.SSSSSS’ format.

Syntax for Extract function

Examples for Extract function in Teradata

(i)Extract Year from Current Date

Since the current date is ‘2019-10-09’ , the extract function returns the year as 2019 as below.

Extract year from Current Date in Teradata
Extract year from Current Date in Teradata

(ii)Extract Year from a Date value

Extract year from a Date in Teradata
Extract year from a Date in Teradata

(iii) Extract Month from a Date

Extract Month from Date in Teradata
Extract Month from a Date in Teradata

(iv) Extract Day from a Date

Extract Day from a Date in Teradata
Extract Day from a Date in Teradata

If we didn’t give the input date value in proper ANSI format, the Extract function will throw the error code 3535 as below.

Error code in 3535 for invalid date field in Teradata
Error code in 3535 for invalid date field

(v) Extract Hour from Current Time

Lets select the current_time in Teradata before run the extract function

Current time in Teradata
Current time in Teradata

In this example, we are going to extract the Hour from the current time. Since the current time is 11:39:47 (HH:MM:SS), the extract function returns the Hour as “11”.

Extract Hour from Current Time in Teradata
Extract Hour from Current Time in Teradata

(vi) Extract Minutes from a Time

Extract minute from a Time in Teradata
Extract minute from a Time in Teradata

(vii) Extract Second from a Time

Extract second from a Time in Teradata
Extract second from a Time in Teradata