Extract function in Teradata with examples
Contents
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
|
1 2 3 |
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
|
1 |
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
|
1 |
Select Extract(YEAR from '2018-10-08'); |
(iii) Extract Month from a Date
|
1 |
Select Extract (MONTH From '2019-08-30'); |
(iv) Extract Day from a Date
|
1 |
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.
|
1 |
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
|
1 |
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”.
|
1 |
Select Extract(HOUR FROM CURRENT_TIME); |
(vi) Extract Minutes from a Time
|
1 |
SELECT EXTRACT (MINUTE FROM '18:29:17.46'); |
(vii) Extract Second from a Time
|
1 |
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
