>>Hi John, at sometime I would like to use the % as a wildcard to pickup all dates if when I need to. Also, I have intentionally set lddate to "%". Thanks for your help.
>
>LIKE and % do matching based on strings. This is not going to work with a date field the same as it wouldn't work with a numeric. In fact, you need to consider datetime fields as a kind of numeric. Sometimes they are really awful to work with, especially when you are doing searches on say a last modified field that can have any time component.
>
>The best I can suggest is to change the comparison from a date field to a character field. This may not give you what you want, but you can do this:
>
>SELECT *
> FROM dbo.test TEST
> WHERE CONVERT(CHAR(20), test.date1, 112) = ?v_date
>
>where v_date has some STRING value that can match the convert string
>
>or
>
>SELECT *
> FROM dbo.test TEST
> WHERE CONVERT(CHAR(20), test.date1, 112) Like ?v_date
>
>where v_date is also some string date with a % also embedded within.
>
>v_date is your parameter field that is passed to ODBC (where the substitution is made prior to sending the query to SQL Server.


I just want to add that you can use SQL Server functions in remote views. Although these queries do get parsed by the query builder a remote view can incorporate sql server functions so the above query would be a valid remote query stored in the DBC. You may not be able to use the query designer to make it but you can add it manually.

What I often do is build a valid query in the designer and then run gendbc which gives me all the fox commands related to the query. Then I cut out the remote view as a separate prg and modify it and then run this to update the dbc to what I want. Of course, after this I can't open the remote view in the designer anymore, but not a big deal.