Examples - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

Example: Returning the Year From the Current Date

The following example returns the year, as an integer, from the current date.

SELECT EXTRACT (YEAR FROM CURRENT_DATE);

Example: Adding 90 days to PurchaseDate

Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.

SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;

Example: Returning an Integer

The following returns 12 as an integer.

SELECT EXTRACT (DAY FROM '1996-12-12');

Example: Character Literal Does Not Evaluate to a Valid Date

This example returns an error because the character literal does not evaluate to a valid date.

SELECT EXTRACT (DAY FROM '1996-02-30');

Example: Character String Literal Does Not Match the ANSI SQL:2011 Date Format

The following returns an error because the character string literal does not match the ANSI SQL:2011 date format.

SELECT EXTRACT (DAY FROM '96-02-15');

If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.

Example: Non-ANSI DateTime Definitions

The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.

SELECT EXTRACT (MINUTE FROM '23:59:17.3');

Example: Returning the Hour From the Current Time

This example returns the hour, as an integer, from the current time.

SELECT EXTRACT (HOUR FROM CURRENT_TIME);

Current time is retrieved as the system value TIME, to the indicated precision.

Example: Returning the Seconds as DECIMAL

The following example returns the seconds as DECIMAL(8,2). This is based on the fractional seconds precision of 2 for CURRENT_TIME.

SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));