SQL类型转换报错处理：提取年份实现指定条件数据查询

Got it, let's tackle this problem head-on. First, let's break down why you're hitting that Msg 245 error:

The YEAR(GETDATE()) function spits out an integer value (like 2018 in your case). When you try to concatenate this integer with varchar strings like '%', SQL Server follows data type precedence rules—it tries to convert the varchar '%' to an integer to match the year value. Obviously, '%' can't be turned into a number, hence the conversion failure.

Step 1: Fix the Conversion Error #

To fix this, you just need to convert the integer year result to a varchar before gluing it to your % wildcards. Use CAST() (or CONVERT() if you prefer) for this quick type shift.

Step 2: Add Previous Year Filter #

To pull records from both the current year (2018) and the prior year (2017), add an OR condition to check for either year string in the Title.

Here's the corrected query that addresses both needs:

SELECT * 
FROM v_AuthListInfo LI 
WHERE Title LIKE '%SUG%' 
  AND Title LIKE '%P1%' 
  AND (
    -- Check for current year
    Title LIKE '%' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '%'
    -- Check for previous year
    OR Title LIKE '%' + CAST(YEAR(GETDATE()) - 1 AS VARCHAR(4)) + '%'
  )

Alternative (More Readable) Approach #

If you want a slightly cleaner syntax, you can use CHARINDEX() to check if the year string exists in the title instead of LIKE:

SELECT * 
FROM v_AuthListInfo LI 
WHERE Title LIKE '%SUG%' 
  AND Title LIKE '%P1%' 
  AND (
    CHARINDEX(CAST(YEAR(GETDATE()) AS VARCHAR(4)), Title) > 0
    OR CHARINDEX(CAST(YEAR(GETDATE()) - 1 AS VARCHAR(4)), Title) > 0
  )

If You Need Fixed Years (Not Dynamic) #

If your requirement is specifically to query 2017 and 2018 (instead of dynamic current/previous year), you can simplify things by hardcoding the years:

SELECT * 
FROM v_AuthListInfo LI 
WHERE Title LIKE '%SUG%' 
  AND Title LIKE '%P1%' 
  AND (Title LIKE '%2017%' OR Title LIKE '%2018%')

All of these queries will resolve the conversion error and return the records you're looking for.

内容的提问来源于stack exchange，提问作者deepti