Convert a Varchar in a DateTime

[joecre]

Hi everybody I need a little help to solve this T-SQL problem.
In my application I have a table with a column that accepts varchar value. I use this column to store value of properties with different data types (Integer, Float ,Varchar and DateTime).
When I try to use a syntax like this to retrieve information from this table I receive a SQL error
"Syntax error converting datetime from character string."

SELECT * FROM tab_val
WHERE ISDATE(val)=1 AND CAST(val As DateTime) > CAST('2003-05-05 08:00:00.000' As DateTime)

where tab_val is my table, val is the column that accepts varchar value and '2003-05-05......' is input value that is a varchar type.

Can anyone suggest me a method to perform this query ?
Actually I compare the column value and the input value as varchar but I'm not sure that is the correct method.
I would to convert the two varchar values in DateTime.
Thank you everybody

[antares686]

Try this

SELECT * FROM tab_val WHERE (CASE ISDATE(val) WHEN 1 THEN CAST(val As DateTime) ELSE NULL END) > SELECT CAST('2003-05-05 08:00:00.000' As DateTime)

CASE will allow the conditional checking of the val column at run time and get past the issue. The way you are currently doign it will still try to CAST to datetime all values.

[joecre]

Your solution work but I had to use the following query

SELECT * FROM tab_val WHERE (CASE ISDATE(val) WHEN 1 THEN CAST(val As DateTime) ELSE NULL END) > CAST('2003-05-05 08:00:00.000' As DateTime)

because after the symbol '>' the keyword SELECT generate the following error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Is this correct ?

Thank you again for your support.

[antares686]

Sorry about that I was testing in sections and left the extra SELECT in by mistake.

[joecre]

I wrote that reply only to describe the syntax and to check for my possible mistakes.
Thank you again.