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

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.

• Approach 1 : Simplest way, cast TIMESTAMP(6) column into string and then type cast into TIMESTAMP(0).

  
  SELECT CAST(CAST(CURRENT_TIMESTAMP AS  VARCHAR(19)) AS TIMESTAMP(0));
   
  Output: 2017-01-19 11:44:28
  

  
  
• Approach 2 : Another way, cast TIMESTAMP(6) column into date and then to TIMESTAMP(0) plus cast TIMESTAMP(6) column to TIME(6) an then subtract interval '00:00:00'.

  
  SELECT CAST(CAST( CURRENT_TIMESTAMP AS DATE) AS TIMESTAMP(0)) + (CAST(CURRENT_TIMESTAMP AS TIME(6)) - TIME '00:00:00' HOUR TO SECOND);
   
  Output: 2017-01-19 11:45:18
  

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