Issue with TIME(6) datatype field in Teradata

Database
Enthusiast

Issue with TIME(6) datatype field in Teradata

Hi All,

I am facing an usual situation in dealing with TIME(6) datatype field in Teradata 12 database. While creating the table I have given TIME(6) datatype to a field. But after creation, If I give SHOW TABLE defintion, The corresponding field datatype is as 'INTEGER FORMAT '99:99:99'.  That too only few times TIME(6) is getting converted into INTEGER FORMAT '99:99:99', some times it is remaining as same TIME(6). Could somebody help me on this.

Please find more detailed Explanation:

I have created table as below

CREATE TABLE ITEDW.SAMPLEL_TIME

(

ID INTEGER,

SYSTEMTIME TIME(6)

)

After creation If I give show table, It is like this

SHOW TABLE ITEDW.SAMPLEL_TIME

CREATE TABLE ITEDW.SAMPLEL_TIME

(

ID INTEGER,

SYSTEMTIME INTEGER FORMAT '99:99:99'

)

Thank you,

Ramkumar

2 REPLIES
Junior Contributor

Re: Issue with TIME(6) datatype field in Teradata

Hi Ramkumar,

this is a known problem due to some settings in the ODBC driver. There are several solutions:

#1: in older versions of SQL Assistant there's "Allow Use of ODBC SQL Extensions in Queries" in SQLA, Options -> Query, uncheck it (this will also disallow non-Teradata SQL like MONTH/RIGHT/LTRIM)

#2: ODBC driver: Options -> disable parsing (afaik the same as #1)

#3: ODBC driver: Options -> DateTimeFormat IAA or AAA

#4: use .NET/JDBC/CLI instead :-)

Enthusiast

Re: Issue with TIME(6) datatype field in Teradata

Hello Dieter,

Thank you very much for your quick response. We tried #2, now the problem got resolved.