EXTRACT Teradata

Compatibility: ANSI

Both DATE and TIME data are special in terms of relational design. Since each is comprised of 3 parts and they are decomposable. Decomposable data is data that is not at its most granular level. For example, you may only want to see the hour.

The EXTRACT function is designed to do the decomposition on these data types. It works with both the DATE and TIME functions. This includes the original and newer ANSIexpressions. The operation is to pull a specific portion of the SQL techniques.

The syntax for EXTRACT:

The next SELECT uses the EXTRACT with date and time literals to demonstrate the coding technique and the resulting output:

1 Row Returnedsyntax for EXTRACT

The EXTRACT can be very helpful when there is a need to have a single component for controlling access to data or the presentation of data. For instance, when calculating aggregates, it might be necessary to group the output on a change in the month. Since the data represents daily activity, the month portion needs to be evaluated separately.

The Order table below is used to demonstrate the EXTRACT function in a SELECT:

EXTRACT function in a SELECT

The following SELECT uses the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY:

4 Rows Returnedaggregates displayed in the GROUP BY

The next SELECT operation uses entirely ANSI compliant code with DATEFORM=ANSIDATE to show the month and day of the payment due date in 2 months and 4 days, notice it uses double quotes to allow reserved words as alias names and ANSIDATE in the comparison and display:

4 Rows Returned

ANSIDATE in the comparison and display

ANSIDATE in the comparison and display

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics