使用带CONVERT或CAST的WHERE子句的T-SQL查询

I'm having trouble with a specific T-SQL Query generated in code.

我在代码中生成的特定T-SQL查询时遇到问题。

Below is the code, the first DECLARE block is sent from code, so those values will change.

下面是代码，第一个DECLARE块是从代码发送的，所以这些值会改变。

The XML business has to do with turning the CSV NVARCHAR into a temporary table.

XML业务与将CSV NVARCHAR转换为临时表有关。

-- DECLARE BLOCK IS SENT BY CODE FROM DIALOG BOX
DECLARE @IdString NVARCHAR(MAX) ='F6LC1'
DECLARE @pm_StartDate DATETIME ='20180201'
-- END DECLARE BLOCK

DECLARE @listOfIds table(entityId nvarchar(50));

DECLARE @x XML 

SELECT @x = CAST('<A>'+ REPLACE(REPLACE(@IdString,' ',''),',','</A><A>')+ '</A>' AS XML)

INSERT INTO @listOfIds
SELECT t.value('.', 'NVARCHAR(50)') AS inVal
FROM @x.nodes('/A') AS x(t)

DECLARE @pm_StartDateTrim NVARCHAR(MAX) = LEFT(CONVERT(NVARCHAR(MAX),@pm_StartDate,112),6)
DECLARE @intStartDate INT = CAST(@pm_StartDateTrim AS INT)

SELECT @pm_StartDate
SELECT @pm_StartDateTrim
SELECT @intStartDate

SELECT   gl.[ACCTNUM]
        ,acc.[ACCTNAME]
        ,gl.[ENTITYID]
        ,CONVERT(INT,gl.[PERIOD]) AS periodConverted
        ,gl.[ACTIVITY]
        ,bldg.[BLDGNAME]
        ,bldg.[BLDGGLA]
        ,gl.[BALFOR]
FROM [dbo].[GLSUM] AS gl
        INNER JOIN [dbo].[BLDG] AS bldg ON gl.[ENTITYID] = bldg.[BLDGID]
        INNER JOIN [dbo].[GACC] AS acc ON gl.[ACCTNUM] = acc.[ACCTNUM]
WHERE gl.ENTITYID IN (SELECT entityId FROM @listOfIds)
        AND gl.BASIS = 'A'
        AND gl.ACTIVITY != 0
        AND gl.PERIOD NOT LIKE '%[^0-9]%'
        AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate
ORDER BY gl.PERIOD ASC

I'm TRYING To only include those records that have a PERIOD which is greater-than or equal-to the provided StartDate.

我正在尝试仅包含那些PERIOD大于或等于提供的StartDate的记录。

The data in the DB for PERIOD is just YYYYMM, so like 201502.

数据库中PERIOD的数据只是YYYYMM，所以和201502一样。

The data provided for the StartDate is DATETIME, so I'm converting to YYYYMM to do the comparison.

为StartDate提供的数据是DATETIME，所以我转换为YYYYMM进行比较。

I'm TRYING to make them both INT so I can compare using < or > or = etc.

我正在尝试将它们都设为INT，因此我可以使用 <或> 或=等进行比较。

So if I leave in the 2nd to last row AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate then i get Conversion failed when converting the varchar value 'R2W274' to data type int.

因此，如果我留在第2行到最后一行AND CONVERT（INT，gl。[PERIOD]）> = @intStartDate，那么当将varchar值'R2W274'转换为数据类型int时，我得到转换失败。

The problem is that there is no value like that in my data.

问题是我的数据中没有这样的值。

When I comment out that line, I get data and R2W274 is not anywhere to be found in any field of any record.

当我注释掉那一行时，我得到的数据和R2W274在任何记录的任何字段中都找不到。

I believe the R2W274 value is actually in the gl.ENTITYID field but not in any of my records, just generally in the DB.

我相信R2W274值实际上在gl.ENTITYID字段中，但在我的任何记录中都没有，通常只在DB中。

So, I'm not sure why this query is even attempting to convert the gl.ENTITYID in the first place.

所以，我不确定为什么这个查询甚至试图首先转换gl.ENTITYID。

Any help?

有帮助吗？

UPDATE:

更新：

I added the recommended addition to the WHERE clause above. I still get the same error.

我在上面的WHERE子句中添加了推荐的附加内容。我仍然得到同样的错误。

1 个解决方案

#1

Please make sure there is no data returning from:

请确保没有返回的数据：

select * from [dbo].[GLSUM] where [Period] = 'R2W274'

Adding a try/catch block like that will help:

添加这样的try / catch块将有助于：

BEGIN TRY  
    -- Generate divide-by-zero error.  
    your select query comes here.  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    print 'error';
END CATCH;

Try/catch block reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

Try / catch块引用：https：//docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

What I would do is to eliminate such data inside where clause:

我要做的是消除where子句中的这些数据：

WHERE gl.ENTITYID IN (SELECT entityId FROM @listOfIds)
        AND gl.BASIS = 'A'
        AND gl.ACTIVITY != 0
        AND gl.[PERIOD] not like '%[^0-9]%'
        AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate

Edit: I was expecting the change in where clause would fix it, surprised why it is not working. Please make sure you added the filter before the one including the conversion. Maybe it would be safer to use a temptable like:

编辑：我期待where子句会修改它的变化，感到惊讶的是为什么它不起作用。请确保在包含转换的过滤器之前添加了过滤器。也许使用像这样的临时表会更安全：

select * 
into #temp_glsum
from [dbo].[GLSUM] gl
WHERE gl.[PERIOD] not like '%[^0-9]%'

and use #temp_glsum in your main query instead of [dbo].[GLSUM]

并在主查询中使用#temp_glsum而不是[dbo]。[GLSUM]

#1