Examples - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
xmd1556127764262.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1211
lifecycle
previous
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));