WHERE clause where the value is convertible

Question

Is there a way in SQL Server using T-SQL say:

 WHERE CONVERT(date, mat1_04_05, 101) = true 

I am making some reports against an application for which I have no source and varchar column, and I cannot rely on user data.

EDIT

I tried using ISDATE . However, I still encounter a conversion error, this is a complete request:

 SELECT mat1_04_01 AS 'CaseStg', matter.mat_no AS 'MatNo', MAX(matter.client) AS 'Client', MAX(mat1_03_01) AS 'InCo', MAX(mat1_07_01) AS 'Clm Amt', MAX(mat1_07_03) AS 'Clm Bal', MAX(mat1_04_05) AS 'BilSnt', MAX(mat1_01_07) AS 'Injured', CONVERT(CHAR, MIN(CONVERT(DATE, usr1_02_01))) AS dos_start, CONVERT(CHAR, MAX(CONVERT(DATE, usr1_02_02))) AS dos_end FROM lntmuser.matter INNER JOIN lntmuser.usertype1 ON lntmuser.matter.sysid = lntmuser.usertype1.mat_id WHERE Isdate(mat1_04_05) = 1 AND Datediff(DAY, CONVERT(DATE, mat1_04_05, 101), Getdate()) > 31 AND mat1_04_01 LIKE 'BILLING MAILED OUT' AND matter.status NOT LIKE 'CLOSED' GROUP BY mat1_04_01, matter.mat_no 

Answer 1

Dude - it makes no sense to use ISDATE() and CONVERT() in the same date field in your WHERE without a control structure. Ie, if ISDATE() = false, then CONVERT() guaranteed to give you a conversion error.

Try the following:

 WHERE ... CASE WHEN ISDATE(myDateField) = 1 THEN DATEDIFF(CONVERT(...)) ELSE 0 END > 31 

Answer 2

You mean checking to see if this column is a date?

 WHERE ISDATE(matl_04_05) = 1 

Answer 3

Use ISDATE .

 ... WHERE ISDATE(mat1_04_05) = 1 

Answer 4

Assuming I understood your original question ...

 IF ISDATE('2009-05-12 10:19:41.177') = 1 PRINT 'VALID' ELSE PRINT 'INVALID' 

source: http://msdn.microsoft.com/en-us/library/ms187347.aspx

Answer 5

You mean something like

 SELECT * FROM FOO where ISDATE(mat1_04_05) 

Answer 6

For datetime you can;

 ;with T(F) as ( select 'cake' union select '01 mar 2011' ) select cast(f as datetime) from T where isdate(F) = 1 >>F >>2011-03-01 00:00:00.000