Removing decimals from datetime in Teradata

General
Enthusiast

Removing decimals from datetime in Teradata

One of my datetime columns is in format -

mm/dd/yyyy hh:mm:ss.000000

How do I get rid of the zeroes? I want my final result in three columns showing-

Col1- mm/dd/yyyy hh:mm:ss

Col2- mm/dd/yyyy

Col3- hh:mm:ss

4 REPLIES
Enthusiast

Re: Removing decimals from datetime in Teradata

You can just select what you want.

SELECT CURRENT_TIMEstamp(0)(FORMAT 'MM/DD/YYbHH:MI:SSBT') ;
SELECT CURRENT_TIMEstamp(DATE) (Format 'MM/DD/YY');
SELECT CURRENT_TIMEstamp(TIME) (Format 'HH:MM:SS');

Rglass

Enthusiast

Re: Removing decimals from datetime in Teradata

@Glass : this did not work. My column name is prod_tm

SELECT prod_tm TIMESTAMP(0) (FORMAT 'MM/DD'YYBHH:MI:SSBT') AS Col1

This did not work! Could you please suggest something else?

Enthusiast

Re: Removing decimals from datetime in Teradata

SELECT prod_tm TIMESTAMP(0) (FORMAT 'MM/DD/YYBHH:MI:SSBT') AS Col1
Teradata Employee

Re: Removing decimals from datetime in Teradata

FORMAT is applied only when you convert to/from [VAR]CHAR:

SELECT CAST(CAST(prod_tm AS FORMAT 'mm/dd/yyyyBhh:mi:ss') AS CHAR(19)) AS Col1