值可转换的WHERE子句

值可转换的WHERE子句

本文介绍了值可转换的WHERE子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

SQL Server 中使用 T-SQL 可以说:

  WHERE CONVERT(date,mat1_04_05,101)= true 

我正在针对我没有源代码的应用程序进行一些报告,列是 varchar ,我不能依赖用户数据。



编辑



我尝试使用 ISDATE 。但是我仍然遇到转换错误,这是完整的查询:

  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 DATEIFE(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


解决方案

Dude - 使用 ISDATE() CONVERT()在您的WHERE中没有控制结构的同一个日期字段。也就是说,如果 ISDATE() = false,则 CONVERT()保证给您一个转换错误。 p>

尝试这样:

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


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
解决方案

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

这篇关于值可转换的WHERE子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-03 06:32