Convert from timestamp with milliseconds and timezone (timestamp(6)) to normal timestamp without milliseconds.

Teradata: Convert Timestamp(6) to Timestamp(0)

Sometime it is required to convert a datatype of a column to TIMESTAMP(0) which is stored as TIMESTAMP(6). By default,TIMESTAMP(6) stores milliseconds. Lets try to achieve this with the help of CURRENT_TIMESTAMP keyword which by default stores current data/time along with milliseconds and timezone.

Example: Sample query for current timestamp showing milliseconds and timezone
SELECT CURRENT_TIMESTAMP;

Output: 2017-01-19 11:43:44.310000+00:00

Normal Casting: Trying to convert timestamp with normal cast but it will fail with 'DateTime field overflow' error.
SELECT CAST(CURRENT_TIMESTAMP as TIMESTAMP(0));

Output:  *** Failure 7454 DateTime field overflow.


Conversion from TIMESTAMP(6) to TIMESTAMP(0) can be achieved in many ways,discussing couple of such approaches.


Note: In above examples, CURRENT_TIMESTAMP can be replaced with a column of datatype TIMESTAMP(6).