CODEWITHSUNDEEP
sqlteradata
Stu
Stu

Reputation: 58

Teradata format string for converting string to timestamp(6)

I have a columnn of timestamps stored as text with no spaces, hyphens, slashes or decimal points, e.g. 20140328160335880258. I want to convert this text to a timestamp in Teradata (v15).

If cut off the microseconds, the following works:

SELECT CAST('20140328160335' AS TIMESTAMP(0) FORMAT 'yyyymmddhhmiss')

However, I can't find a format string that allows the partial seconds to be included in the timestamp:

SELECT CAST('20140328160335880258' AS TIMESTAMP(6) FORMAT 'yyyymmddhhmiss') 
> SELECT Failed. 6760: Invalid timestamp

SELECT CAST('20140328160335880258' AS TIMESTAMP(6) FORMAT 'yyyymmddhhmissssssss') 
> SELECT Failed. 3350: Invalid FORMAT string

I've tried 'yyyymmddhhmiss.ssssss', 'yyyymmddhhmiss.s(6)', 'yyyymmddhhmisss(6)' and 'yyyymmddhhmissffffff', but all are invalid.

Is the only option to insert a decimal point into the text version of the timestamps?

Upvotes: 1

Views: 8622

Answers (1)

dnoeth
dnoeth

Reputation: 60462

There's no way using Teradata's FORMAT without adding a separating period.

But there's also TO_CHAR:

TO_TIMESTAMP('20140328160335880258', 'yyyymmddhh24missff6')

Upvotes: 3

Related Questions