Searching for 'm/dd/yy' through different datatypes

[LizKayl]

I'm trying to complete a search through fields of different types to see if the search value is found. It can be anything the user wants to enter. The problem is the backslashes I believe, making it not read the query properly. How can I search?

select fields from table where fieldA like '%d/m/yy%'....

I've tried typecasting the fields or the var to varchar and then comparing. But I need to find the date in either a datetime field or a text field. Typecasting the search string as a varchar works if I have "fieldA = cast('datestring' as varchar)" but then I get errors for the non-date string searches.

 

[cheyney]

what are the errors? Are some of them "Insufficient result space..."? When you don't specify a length in your cast to varchar, it assumes 30 and will error if the string created would be longer than this.

Try "fieldA = cast('datestring' as varchar(1000))"

Cheyney

 

[SQLBill]

Here's the first problem:

It can be anything the user wants to enter.

It SHOULDN'T be anything a user inputs. You should validate what they are inputting.

for example:

DECLARE @input_date VARCHAR(25)
DECLARE @new_date DATETIME
SET @new_date = (SELECT CAST(@input_date AS DATETIME))

*Disclaimer, above is just as an example, I would test it first.

-SQLBill

 

[LizKayl]

I ended up typecasting the fieldtype and checking if the string was numeric with 2 '/'s. If it was then I compared equal to the typecast string. Otherwise, I compared if it was "like '% non-type-cast-string %'

e.g "select convert(varchar, fieldA, 1) as fieldA from Table"