CODEWITHSUNDEEP
teradata
Mr.Doge
Mr.Doge

Reputation: 57

In Teradata, trying to convert Timestamp(6) to timestamp(0)

A is in the format of timestamp(6). I need it in timestamp(0). The code I am using is the following:

cast(cast(A AS date) as timestamp(0))
FROM 'table'
where A >= '?StartDT'

After inputing the date I want for the parameter I get the 'Invalid timestamp' error.

Upvotes: 0

Views: 5675

Answers (1)

JNevill
JNevill

Reputation: 50009

If A is truly a Timestamp(6) then casting it first as a DATE will affectively trim off the time elements, so when you cast the result to a TIMESTAMP(0) you are going to end up with a time of 00:00:00.

You'll need to also cast the TIMESTAMP(6) field as a time and then add the results together like:

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

You can also use SUBSTRING() to snip off the last 6 characters of the TIMESTAMP(6) field and cast that resulting string to a TIMESTAMP(0):

CAST(SUBSTRING(CAST(A AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))

This doesn't address the INVALID TIMESTAMP error you are getting though. Are you certain that field A is a TIMESTAMP(6) and not a VARCHAR() that looks like a Timestamp? What happens when you remove the outer cast, are their any dates in the result that look like they wouldn't convert nicely to a timestamp? Something is not quite right here, and I suspect that it's in your data.

Upvotes: 2

Related Questions