ODBC datetime format 'III' causing timestamp to time conversion to fail?

[jennifer_ma]

using SQLA 13.11.0.33 with ODBC version 13.10.00.05

I was getting a 5407 from doing this

sel cast( current_timestamp as time)

but

sel cast( current_timestamp as timestamp)

sel cast( current_timestamp as date)

both work fine, so looking at my odbc settings, default mode is teradata,

I had datetime format defaulted to 'III'

which shouldn't matter since i'm not creating any tables here?

out of something to try I changed it to 'AAA'

which caused the first statement to return without error

So i've checked by changing the datetime format setting backwards and forwards between 'AAA' and 'III'

and 'III' is definitely causing the first statement to fail with error 5407

can anyone shed any light on this behaviour?

Jennifer

[dnoeth]

Hi Jennifer,

when you check DBQL for the bad query you'll probably see that the ODBC driver replaced TIME with INT, this setting is not only for DDL :-)

'III' should not be used anymore (it's just a stupid old default), better use 'AAA' or 'IAA'.

Or set another ODBC option "Disable Parsing", which also switches off other ODBC functions like RTRIM, LENGTH, MONTH, etc.

Dieter 

[sachin_sebastia]

Hi Dieter,

Recently came across this issue and am changing the Format.

Can you please let me know what the formats III, IAI, AAA, IAA etc mean / How they are different?

[dnoeth]

It's the default formats for DATE/TIME/TIMESTAMP (in that order) and indicates INTEGER or ANSI.

For DATEs both is ok (similar to SET SESSION DATEFORMAT), but for TIME and TIMESTAMP ANSI is preferred, I was used historically before there was the datatypes TIME and TIMESTAMP.

Dieter