Trying to use a typecase Inside a "like '% blah %' tag.

[LizKayl]

My subject says it mostly. I have a form where the user can search by any keyword and wants to be able to seach by a date of the form "dd/mm/yy". Because of the slashes, I am getting no response. So I am trying to cast the search string as a varchar so it will compare to the characters and not ignore the numbers after the '/'s. The value being searched for is inside of a text field, but is not the only text.

This is what I have. I've tried it with and without the two single quotemarks.

select * from table where comment_field like '%cast(''3/3/04'' as varchar) %'

 

[JayKusch]

I am not sure why you are trying to use the CAST?

Will this work ...

select *
from table
where comment_field LIKE '%3/3/04%'





Thanks

J. Kusch

 

[LizKayl]

For some reason it's not finding them. I believe it's because of the slashes ('/'). That was my first thought before I realized that it was a problem.

 

[JayKusch]

It has nothing to do with the /. I am able to run your example in test without an issue. If I were to guess ... I would say the date you are looking for may be formatted diffrenetly from what you are expecting. Like ...

2004/03/03 or 03/03/04 (mm/dd/yy) or 03/03/04 (dd/mm/yy).

The code worked great. Here is what I used ....

select * 
from table 
where comment_field LIKE '%3/3/04%'

Thanks

J. Kusch

 

[LizKayl]

The data is in a text field. And it finds it sometimes but not always. The inconsistency is confusing. For other dates (i.e. 10/6/03 etc) it returns most of the values. But it doesn't return the values when the 3/3/04 is in the comment field and half? of the other ones, with no apparant rhyme or reason.

 

[dcusick]

The problem here is that you are using a Text Field. Therefore, the date isn't displayed in the same format everytime. The date is displayed however you enter it into the DB. If you enter '03/03/2004' into the database, then that's what will be stored. The only way you'll be able to pull this up with the statement you are using is with LIKE '%03/03/2004%'. If you use LIKE '%3/3/04%' it will not find it, nor will LIKE '%03/3/04%', or any variation. Your best bet is to make it a DateTime field, that way you'll have the same format, according to your regional settings, no matter what. Hope this helps, and good luck!

Doug