All Forums Tools
EUsha 25 posts Joined 03/14
12 Sep 2014
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
TD Sql Assistant version:
ODBC Provider Version : ODBC
Teradata .Net Provider Version : Teradata .Net
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.

Raja_KT 1246 posts Joined 07/09
12 Sep 2014

It is always good to check the readme documents of  ODBCs..., like in
I trust Linux/Unix more and I am a big fan of them. So much flexibility of programming and work arounds :)

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
12 Sep 2014

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'


You must sign in to leave a comment.