17.10 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

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));