Archives of the TeradataForum

Message Posted: Fri, 01 Sep 2006 @ 15:17:37 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Ok here's the scenario. it's a bit complicated but I'm sure that lots of people have the same issue and someone has some good advice.

Our ODBC settings are set to III (default) .

Currently we have some canned, custom SQL reports that use timestamps as part of a join. They are of data type TIMESTAMP(6). However, the level of the join needs to happen at a TIMESTAMP(0) level.

So. The we do the following in the join condition.

     Tstamp1  is TIMESTAMP(6).

     Where ...

     and
             Cast((cast tstamp1 as char(19)) as timestamp(0))
             BETWEEN  Cast((cast tstamp2 as char(19)) as timestamp(0))
                       AND Cast((cast tstamp3 as char(19)) as timestamp(0)).

I know this isn't clean because the Timestamp(6) is really 26 in length. I tried to substring it properly, but ended up with the same result.

I can make this scenario work with dbc tables, or cast(CURRENT_TIME as timestamp(0)) with no error.

However using complex SQL statements (using multi-view joins) it looks like when the query is rewritten by ODBC it messes up and returns

The familiar "3617 ERROR Format 'YYYY-MM-DD' does not match the data type."

If I Set the ODBC driver date time format to "AII" this works. I can understand why maybe , in that for some reason identifying the Date portion of the string as ANSI gets around the error. But I am paranoid about changing the ODBC settings enterprise wide, for fear of creating new problems.

My two questions are:

1) Has anyone encountered this type of problem and found a workaround for the above example without changing ODBC driver settings.

2) Has anyone switched to AII or AAA or AIA from IIII and had any issues, problems, or strange effects.

Eric W. Barner
Senior Teradata DBA
HCA
Information Technology & Services

	<Prev	Next>		<<First	<Prev	Next>	Last>>