>>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.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only