Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

Database

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,
Tags (2)
6 REPLIES
Junior Contributor

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

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

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

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.
Teradata Employee

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

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

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

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

Enthusiast

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

If the field is in timestamp (6) it is because it is a valid value.

 

SELECT CAST(CAST(FIELDTOCHANGE AS VARCHAR(19)) AS TIMESTAMP(0))

 

It does not generate error, because the field in timestamp (6) already would have failed.

 

 

Teradata Employee

Re: Converting a TIMESTAMP(6) to a TIMESTAMP(0) for Insert

The subtraction converts the data type from "TIME" to "INTERVAL HOUR TO SECOND".  (You can't add TIMESTAMP + TIME, but you can add TIMESTAMP + INTERVAL.)