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

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 microseconds. Lets try to achieve this with the help of CURRENT_TIMESTAMP keyword which by default stores current data/time along with microseconds and timezone.

Example: Sample query for current timestamp showing microseconds and timezone


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.


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