Toad World® Forums

Query Builder Date Default

#1

Toad Date Point 4.2

I am using Toad Data Point to connect to Teradata. When I use query builder and try to filter on a data field by entering 2019-03-01 the tool automatically formats the date to '2019-03-01 00:00:00' and the query will not run from the diagram giving this error message - [Teradata Database] [3535] A character string failed conversion to a numeric value. I have to go into Query mode and remove the 00:00:00 and run the query from there.

I have tried to set the format in Options\Environment\Grid\Data Type Formatting \DataTime to yyyy-MM-dd which shows the correct format of 2019-03-01.

Is there a way to set it so it does not add the time so the query will run in diagram mode.

#2

Hi jim.payne,
When you use filter you need use same format as column format.
If you need filter by date you must convert datetime value to date for example:

cast("CONTACT"."BIRTH_DATE" as Date)='1949-12-23'

As you see in this picture.
image

Petr Hodovsky

#3

When I put 2019-04-01, query builder converts it to '2019-04-01 00:00:00'
If I go to the Query table I can remove the 00:00:00 and the query runs fine without doing a cast statement. There must be something in Toad to keep Query Builder from formatting the data this way. I have other users that do not have this problem but they have not updated of late.

#4

I testing on TDP 5.0. Can you test on 5.0? What type has the column? If it is TIMESTAMP it must have time part. If you made query where is TIMESTAMP column without time part you get this error "[Teradata Database] [6760] Invalid timestamp.". Query builder in new version add time part automatically.