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

Extract (YEAR/MONTH/DAY From <Input_Date_value>)

Extract (HOUR/MINUTE/SECOND/TIMEZONE_HOUR/TIMEZONE_MINUTE From <Input_Time_Value>)

Examples for Extract function in Teradata

(i)Extract Year from Current Date

Select Extract(YEAR FROM CURRENT_DATE);

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

(ii)Extract Year from a Date value

Select Extract(YEAR from '2018-10-08');

(iii) Extract Month from a Date

Select Extract (MONTH From '2019-08-30');

(iv) Extract Day from a Date

Select Extract (DAY From '2019-07-26');

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

Select Extract (DAY From '19-07-26');

(v) Extract Hour from Current Time

Lets select the current_time in Teradata before run the extract function

SELECT CURRENT_TIME

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”.

Select Extract(HOUR FROM CURRENT_TIME);

(vi) Extract Minutes from a Time

SELECT EXTRACT (MINUTE FROM '18:29:17.46');

(vii) Extract Second from a Time

SELECT EXTRACT (SECOND FROM '18:29:17.46');

Recommended Articles

• How to add months or days to date in Teradata using Add_months and Interval function?
• Timestamp in Teradata with examples
• Timestamp to Date conversion in Teradata
• How to get the current date in Teradata with examples