Toad World® Forums

Teradata Date error in WHERE


#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