All Forums Database
saadwaseem 2 posts Joined 09/12
27 Mar 2013
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?

CarlosAL 512 posts Joined 04/08
27 Mar 2013

Using SQLA through ODBC perhaps?
If so, check options DateTime Format.
HTH.
Cheers.
Carlos.

dnoeth 4628 posts Joined 11/04
27 Mar 2013

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
 

Dieter

27 Mar 2013

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

dnoeth 4628 posts Joined 11/04
27 Mar 2013

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

Dieter

27 Mar 2013

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 

dnoeth 4628 posts Joined 11/04
27 Mar 2013

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

Dieter

You must sign in to leave a comment.