WHERE clause where value is convertible

问题:

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

WHERE CONVERT(date, mat1_04_05, 101) = true

I'm doing some reporting against an app that I don't have source for and the column is varchar and I can't rely on user data.

EDIT

I tried using ISDATE. However I'm still running into a conversion error this is the full query:

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  

回答1:

Dude -- it doesn't make sense to use ISDATE() and CONVERT() on the same date field in your WHERE without a control structure. I.e., if ISDATE() = false, then CONVERT() is guaranteed to give you a conversion error.

Try this:

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

回答2:

DO you mean check to see whether that column is a date??

WHERE ISDATE(matl_04_05) = 1

回答3:

Use ISDATE.

... WHERE ISDATE(mat1_04_05) = 1

回答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

回答5:

Do you mean something like

SELECT * FROM FOO where ISDATE(mat1_04_05)

回答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