TIME (0) field converted to INTEGER

Database

TIME (0) field converted to INTEGER

Hello,

I'm facing a pretty strange issue here, wanted to see if anybody had any insight on this. 

I'm creating a TD table with a TIME(0) field in it. Strangely, once the CREATE TABLE is complete, the table is created with a INTEGER field instead.

Table Create SQL:

CREATE SET TABLE REVENUE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
record_id INTEGER,
start_dt DATE FORMAT 'YYYY-MM-DD',
start_tm TIME(0),
revenue DECIMAL(18,4))
PRIMARY INDEX ( record_id );

Show Table result:

CREATE SET TABLE REVENUE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
record_id INTEGER,
start_dt DATE FORMAT 'YYYY-MM-DD',
start_tm INTEGER FORMAT '99:99:99',
revenue DECIMAL(18,4))
PRIMARY INDEX ( record_id );

I have tried this from multiple computers with the same result. Any idea?

6 REPLIES
Junior Supporter

Re: TIME (0) field converted to INTEGER

Using SQLA through ODBC perhaps?

If so, check options DateTime Format.

HTH.

Cheers.

Carlos.

Senior Apprentice

Re: TIME (0) field converted to INTEGER

As Carlos said, change the DataTime Format to AAA instead of the old default of III.

Or mark the "Disable Parsing" option in ODBC, this also switches off other ODBC syntax like the LENGHT, MONTH or LTRIM function.

Dieter

Enthusiast

Re: TIME (0) field converted to INTEGER

Hi Dieter,

Out of curiousity of the above problem , 

In order to change the Datetime format to AAA  in SQLA, 

I went in Tools -->options-->General--> datetime format but i was

not allowed to input any value other than Y,M or D..

Did u mean the same thing ? please suggest.

Also in order to  mark the "Disable Parsing "  in SQLA ,  whats the path in SQLA?

Thanks in advance..

cheers!

Nishant Bhardwaj

Senior Apprentice

Re: TIME (0) field converted to INTEGER

Hi Nishant,

both are ODBC options, you'll find them on the default options page for your data source.

Btw, when you switch to .NET you get this by default, it's faster and the editor is showing the actual position of a syntax error, too. 

Dieter

Enthusiast

Re: TIME (0) field converted to INTEGER

Ok Got it... thanks!

So i belive If we execute the above tables in BTEQ then there wont be such kind of issues in creating TIME  datatype as there is no cocncept of ODBC connectivity in BTEQ.

Please correct me if I am wrong...

Cheers!

Nishant 

Senior Apprentice

Re: TIME (0) field converted to INTEGER

Hi Nishant,

yes, only ODBC is behaving this way, any other connection type, either CLI, .NET or JDBC don't have that problem.

Dieter