Query not working in Teradata SQL Assistant with Teradata .Net while the same query is working with ODBC connection & vice versa

Tools
Enthusiast

Query not working in Teradata SQL Assistant with Teradata .Net while the same query is working with ODBC connection & vice versa

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 13.11.0.1

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.

2 REPLIES
Enthusiast

Re: Query not working in Teradata SQL Assistant with Teradata .Net while the same query is working with ODBC connection & vice versa

It is always good to check the readme documents of  ODBCs..., like in http://downloads.teradata.com/download/connectivity/

http://downloads.teradata.com/download/connectivity/odbc-driver/windows

http://downloads.teradata.com/download/connectivity/net-data-provider-for-teradata

I trust Linux/Unix more and I am a big fan of them. So much flexibility of programming and work arounds :)

Senior Apprentice

Re: Query not working in Teradata SQL Assistant with Teradata .Net while the same query is working with ODBC connection & vice versa

This is from SQL Assistant's help:

Teradata.NET Specific Limitations

•  TD.NET always uses ANSI date mode. All date literals must be entered as YYYY-MM-DD. 

Neither '01/01/2010' nor '2000-01-01' are DATEs, but strings and must be casted to a DATE.

The only reliable way to write a DATE is Standard SQL's DATE '2000-01-01'