All Forums Database
InTransition 2 posts Joined 03/11
23 Mar 2011
Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

Hello Folks,
I am new to Teradata.

I have a date column in one table (Table A) that I have to insert into a date column in another table (Table B)

However,
Table A date type: TIMESTAMP (6)
Table B date type: TIMESTAMP (0)

Whatever I try it keeps giving me a datetime field overflow error. Does anyone know how can I convert a timestamp(6) to a timestamp(0).

Thanks,

dnoeth 4628 posts Joined 11/04
23 Mar 2011

There's no easy way to truncate a TimeStamp :-(

Using an intermediate string:
CAST(SUBSTRING(CAST(x AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))

or
CAST(CAST(x AS DATE) AS TIMESTAMP(0))
+ (CAST(x AS TIME(6)) - TIME '00:00:00' HOUR TO SECOND)

I prefer #2 because #1 involves an intermediate string (additionally might need a FORMAT) and probably uses more CPU).

Dieter

Dieter

InTransition 2 posts Joined 03/11
24 Mar 2011

Thanks Dieter,
On the same lines, what I ended up using was this:

CAST(CAST(TS_6 AS DATE) AS TIMESTAMP(0))

Basically it was a 2 step process from timestamp(6) to DATE and then from DATE to timestamp(0).

We dont really care about the time right now so it was easy for me to get away with not capturing the time portion of it.

Thanks for your input.

IrfanAlee 7 posts Joined 03/10
24 Feb 2014

I usually prefer using this 
CAST(CAST(DTTM AS VARCHAR(19)) AS TIMESTAMP(0)). i am not quite sure about time it take or how CPU intensive is the this process
 
Regards,
Irfan

sauhard 1 post Joined 01/14
07 Jan 2016

hi Dieter,
Could you please explain the need of function of the string "- TIME '00:00:00' HOUR TO SECOND" in the #2 conversion.
thanks

You must sign in to leave a comment.