Teradata Date Time Part 12 - Casting Date Time interval and EXTRACT ing HOUR,MINUTE and SECOND

Conversion between different types of day-time interval types is easily accomplished using the CAST feature.

Conversions which include a remainder which is not supported by the conversion interval type will be dropped, not rounded. 

You can CAST any of the values in the left column AS any of the values in the right column.

CAST

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

AS

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY)

Result is 2

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY);

Result is 1 day --> No rounding

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY TO MINUTE);

2 02:00

• Extracting from TIME.

We can extract number of Hours, Minutes and seconds from the TIME data type using the EXTRACT function.

We can extract from the TIME data type of from the calculation that results in time data type.

Examples:

SELECT EXTRACT ( HOUR FROM TIME '12:12:12'); --> 12

SELECT EXTRACT ( HOUR FROM (TIME '12:12:12' + INTERVAL '4' HOUR)); --> 16

SELECT EXTRACT (MINUTE FROM CURRENT_TIME);

SELECT EXTRACT (SECOND FROM CURRENT_TIME);