Trying to do a criteria search on a DATE field

Toad for IBM DB2
1

I am trying to do a Where Criteria lookup on a field in our db that is typed as a DATE field. However, the data in the DB does not have any timestamp information in it. I am unable to figure out how to do a date search without the 00:00:00 that Toad automatically adds onto the criteria. With this extra bit of criteria it cannot find any data in that field. I tried just typeing the date in the query, but it gives an error about not being a valid string. When I use the form tab to input a constant, it formats the query as “WHERE (ARTBASE.FROM_DOS_DT = ‘2011-04-01 00:00:00’)”. How can I do a search on a date field that has data in the format of “04/01/2011”? Thank you.

2

There is an issue in the Query Builder when creating the where clause for DB2 DATE columns. I have opened CR 85015 to track this specific issue.
To work around this you can type in a value enclosed in aposts or first select a value from the drop-down list and then enclose that value in aposts.
See attached for screenshots.
Thanks
Jeff Message was edited by: jeff.podlasek

QBPost1.png
QBPost1.png799×302 17.4 KB

3

There is an issue in the Query Builder when creating the where clause for DB2 DATE columns. I have opened CR 85015 to track this specific issue.
To work around this you can type in a value enclosed in aposts or first select a value from the drop-down list and then enclose that value in aposts.
See attached for screenshots.
Thanks
Jeff Message was edited by: jeff.podlasek

QBPost3.png
QBPost3.png909×302 15.9 KB

4

There is an issue in the Query Builder when creating the where clause for DB2 DATE columns. I have opened CR 85015 to track this specific issue.
To work around this you can type in a value enclosed in aposts or first select a value from the drop-down list and then enclose that value in aposts.
See attached for screenshots.
Thanks
Jeff Message was edited by: jeff.podlasek

QBPost2.png
QBPost2.png1113×689 46.2 KB

5

When I try to use the quotations, I get a syntax error that says

Category Timestamp Duration Message Line Position
Error 5/10/2011 4:01:19 PM 0:00:00.203 DB2 Database Error: ERROR [22007] [IBM][DB2/LINUXX8664] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
1 0

I tried single and double quotes. If I try to use the drop down box, it just sits there and thinks forever. I am assuming this is because of the amount of different values in the DB.

6

Can you let us know what version of Toad DB2 you are using? That can be found on the help-about dialog.
Also, can you send in screenshots of when you placed those aposts on the Query Builder contgrol and of the query as it was formatted when you placed single aposts around the value?

thanks

7

I am using Toad Version 4.7.1.638. I am not sure how to attach photos, so I created an album in the photo section called Date Criteria. I gave a few shots showing the properties of the field as well as some sample data. I also showed the error I get for double and single quotes. And I showed how it formats it if I use no quotes.

Thanks.

8

I am having a hard time viewing your screen shots.
I am thinking that if you format your manually entered Query Builder ‘where clause’ dates such as:
‘01/01/1987’ - ie: ‘mm/dd/yyyy’ - that work around would work for you.
Please let me know if that is not the case.
Thanks
Jeff