I am facing an issue where i have a query which has the WHERE clause defined as WHERE sample_dt <> '01/01/2010' (that is with format 'DD/MM/YYYY'). This query when run in TD Sql Assistant with ODBC connection provider was running perfectly but the same query when with Teradata .Net provider it was failing with below error:
SELECT Failed. 3535: A character string failed conversion to a numeric value.
Hence i changed the format of the DATE value as '2000-01-01' and the query ran successfully for some users with both the connectivities (ODBC & .Net).
But on further run of the query by other users I have noted that this same modified query was NOT working with ODBC connection but it worked with Teradata .Net connection. Now my question is, with the same environment for the users on versions of the tool and the connectivity providers given below, why are we noticing the difference in execution of the query.
System type: 32-bit operating system.
Database version: Teradata 14.10.01.07
TD Sql Assistant version: 13.11.0.04
ODBC Provider Version : ODBC 13.10.00.06
Teradata .Net Provider Version : Teradata .Net 126.96.36.199
Further when i changed the DATE format in underlying table definition from
sample_dt DATE FORMAT 'DD/MM/YYYY' to sample_dt DATE FORMAT 'YYYY-MM-DD', the query ran perfectly for all the users.
But I wanted to know without the change in formats in table definition, why are we noticing these errors for users using the similar environment as stated above. Please explain.
I assume that ANY Teradata Query SHOULD work on both ODBC and .Net provider. With this change does it mean that there are few features that will not be supported with Teradata .Net connectivity or with ODBC connectivity. If so, can someone tell me what other features will not work and what care should be taken while writing my queries to ensure that they work with both the connections.
It is always good to check the readme documents of ODBCs..., like in http://downloads.teradata.com/download/connectivity/
I trust Linux/Unix more and I am a big fan of them. So much flexibility of programming and work arounds :)
This is from SQL Assistant's help: