"Invalid date supplied for" error

Connectivity

"Invalid date supplied for" error

My database is a Teradata version 13.00.01.19

I retrieve data with a MS Excel v2003 file (using MS Query)

Before to do that, I installed "the tdodbc__windows_i386.13.10.00.05" products on my desktop : Teradata Client 13.10, Teradata 13.10.00.05 ODBC driver. I created a System DSN etc.

The connection to the database is OK, I can query from MS Excel (through Teradata Client, ODBC and MS Query), I retrieve data, perfect !

But ...

I have an issue when I try to retrieve data from MS Query with a parameter in my SQL pointing to a DATE column (my TABLE.COLUMN is a DATE field). It's OK with another type of column.

In the Options of the configuration of my ODBC system data source I tested the different values, but the issue persists.

in the Session Mode, I tried "ANSI", "System Default", "Teradata"

in the DateTime format I tried "AAA", "III" (but not all the list ...)

With some tests I have this error in a MS Query alert box : "Invalid date supplied for TABLE.COLUMN"

With other tests I have this error : "Invalid date/time/timestamp value"

Is there a solution ? where is my mistake ?

Thanks for your help.

(sorry for my English/American)

8 REPLIES
Teradata Employee

Re: "Invalid date supplied for" error

The problem is that MS Query binds "DATE" columns as SQL "TIMESTAMP", while in Teradata these are completely different data types. Under Options, Advanced there is a checkbox to "Use DATE data for TIMESTAMP parameters" which in effect causes the driver to truncate anything passed as TIMESTAMP to just the DATE portion. Provided that you are not acutally using any TIMESTAMP fields, setting this option should work around the issue.

If you also use other query tools that do support separate DATE and TIMESTAMP types, then you won't want this option set. So use a different ODBC DSN for MS Query.

Re: "Invalid date supplied for" error

I had tried this option too but it didn't work.

To be sure, I did several tests again but ... still I have the error : "Invalid date supplied for TABLE.COLUMN".

I don't know what to do ...

I don't success in querying my Teradata database from Excel-MSQuery with a prompt waiting for a DATE.

Re: "Invalid date supplied for" error

reminder : the issue comes up only with the prompt. When I write the date (eg 2012/09/09 or 09-09-2012, I tried different formats) directly in the SQL code, all is OK.

My WHERE clause is like that in the MS Query : "... WHERE TABLE.COLUMN = [Date ?]" . If I put a cast for the parameter, it doesn't work either

Junior Contributor

Re: "Invalid date supplied for" error

I don't know how to fix this error, but you might try changing the parameter type from DATE to STRING and let do Teradata the cast. I don't know how this is specified in Access (i don't use Access), but something like this could work:

WHERE table.column = cast(? as date format 'yyyy-mm-dd')

Maybe you have to add single quotes

WHERE table.column = cast('?' as date format 'yyyy-mm-dd')

Dieter

Fan

Re: "Invalid date supplied for" error

Hi,

I want to cast the TIMESTAMP '9999-12-31 11:59:59' as timestamp but it says invalid date supplied

sel CAST('9999-12-31 23:59:59' as TIMESTAMP)

but when i tried this

sel CAST('9999-12-31 17:59:59' as TIMESTAMP)

it is working till this time only after which it throws invalid timestamp error is there any way i can cast value as timestamp

Thanks in advance

Ashwin

Junior Contributor

Re: "Invalid date supplied for" error

Hi Ashwiin,

see

http://forums.teradata.com/forum/database/invalid-timestamp-literal-when-defaulting-a-timestamp-colu...

Btw, please open a new thread for a new question.

Dieter

Re: "Invalid date supplied for" error

Hi Dieter,

It doesn't work, MS Query seems to not understand the 'cast' keyword in its SQL code.

In the different layers I have (from Teradata DB on the server to MS Excel 2003 on my PC), I think that the issue is only coming from the MS Query layer, this Microsoft product itself or its configuration on my desktop computer.

I continue my research on this side, if I find the solution I'll be back

BR

Junior Supporter

Re: "Invalid date supplied for" error

Hi.

I executed your query in a MSQuery window with no errors (only replaced 'SEL' with 'SELECT', I'm a purist!).

Execute SQL window:

"SELECT CAST('9999-12-31 17:59:59' as TIMESTAMP);"

Query1 (result) window:

"'9999-12-31 17:59:59,000000'"

"9999-12-31 17:59:59,000000"

BTW: Note that TIMESTAMP is not the same as TIMESTAMP(0).

HTH.

Cheers.

Carlos.