Teradata Date error in WHERE

Toad Data Point
1

Teradata table shows date field as DATE type. Looking in Teradata directly I see a date, as in 11/10/2015.

I notice that Toad Data Point is showing the date as 11/03/2015 12:00:00:00 am, but that seems like it would be ok, as the SQL generated from using a WHERE clause is consistent:

where process_dt = calendar_gregorian_yesterday and that variable shows “between 11/09/2015 12:00:00:00 am and 11/09/2015 23:59:59:998”

the only difference I see is that no “am” in the variable.

Anyway, when I run using the WHERE, I get:

Got error 4208 '{0} ODBC data source returned an error: Invalid date supplied for TABLEA.PROCESS_DT. ’ from HUB

2

How Toad displays dates depends on local settings. Date format in WHERE clause depends on query type. If it’s a cross-query then MySQL date/time literals have to be used in WHERE clause.

What query do you execute? It looks like it’s a cross query. So MySQL date literals should be in the WHERE clause.

Aleksey

3

Ok, you are correct. I was in cross-query mode (even though I was only accessing one database). When I switched to query mode, it worked.

So help me understand what is behind this. You are saying that in cross-connect mode it is necessary to have all the SQL be MySQL compatible? Shouldn’t Toad Data Point take care of that “behind the scenes” when I bring up the Where Clause box and select Date Range – Yesterday? If not, does anyone know if this is on a future release enhancements list?

That being said, how would I handle my current scenario where I am attempting to compare a Teradata data (process_dt) to yesterday (11/10/2015)?

Is it the process_dt that needs to be converted to a MySQL format, or the literal, or both?

From: Aleksey Bazhenov [mailto:bounce-AlekseyBazhenov@toadworld.com]

Sent: Wednesday, November 11, 2015 2:03 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Teradata Date error in WHERE

RE: Teradata Date error in WHERE

Reply by Aleksey Bazhenov

How Toad displays dates depends on local settings. Date format in WHERE clause depends on query type. If it’s a cross-query then MySQL date/time literals have to be used in WHERE clause.

What query do you execute? It looks like it’s a cross query. So MySQL date literals should be in the WHERE clause.

Aleksey

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

4

If you use tables from the same connection you don’t need to use the cross-query mode.

What is the type of the process_dt? If it’s DATE then you should be able to see the predefine date range constants like Yesterday, Last week, Last month … and use them when you create WHERE clause. But if the type is TIMESTAMP then you should create a calculated field to cast Timestamp column to Date type like

Cast(process_dt as Date) as process_dt_2

And use it in the where clause

WHERE (process_dt_2 = (current_date - interval ‘1’ day))

Regards

Aleksey

5

When you created Date_Table , the datatype you specified is date. May be the datatype of table a is is VARCHAR , which is not matching with Date_Table . That is the reason you are unable run INSERT statement. We can go for CAST .

INSERT INTO Date_Table
select
  a.customer_field,
  a.CAST (cast(date_field as varchar(8)) AS DATE FORMAT 'yyyy-mm-dd'),
  a.other_fields
from a

I had the same issue, but I found out it had nothing to do with the content of the date-fields. In my case, the order of the ' create table '-attributes and ' insert '-attributes was not the same, which caused this error statement.

create multiset table database.table ,fallback ,
     no before journal,
     no after journal,
     checksum = default,
     default mergeblockratio
     ( 
      selection_dttm date,
      strategy_nm varchar(30) character set latin not casespecific
     );

and then the 2 attributes in the insert statement in different order:

insert into database.table
select 
    strategy_nm,
    selection_dttm
from source_table

If you want to learn more about Terada, my suggestion is to go for Teradata Certification Training

1 Like