DATE to NUMERIC conversion in Teradata

DATE format can be converted into SMALLINT, BYTEINT, INTEGER,DECIMAL(n,m), or FLOAT datatypes.

When a date is converted to a numeric,it will be encoded using the following formula:
(year - 1900) * 10000 + (month * 100) + day


Note:
A FORMAT phrase in DATE to numeric conversion may only contain the 9 formatting character.
No error is generated on conversion of DATE to INTEGER or FLOAT.

SELECT CAST (DATE '1985-12-31' AS INTEGER FORMAT '9999999');

output: 851231


Generally. the above conversions are not recommended. However it gives the idea how we can change to a different format.

SELECT CAST (DATE '2041-03-30' AS INTEGER FORMAT '9999999');

output: 1410330


If you see the below code, it will result in undesired output.

SELECT CAST (DATE '1976-07-04' AS INTEGER FORMAT '9999999');

output: -1239296



..