SQL Server 2019含非日期数据字段的日期查询报错求助

问题场景 #

在SQL Server 2019中需要查询近2天的事件数据，但没有专属datetime字段，仅有的日期时间信息夹杂在其他字段中。提取后的日期格式为2022-08-07T23:03:36-07:00，尝试查询时出现报错：

Conversion failed when converting date and/or time from character string.

原SQL语句存在多处问题：

SELECT 
    resource_key
    , SUBSTRING(last_actions,3,13) as 'Action'
    , SUBSTRING(last_success,actions,27,25) as 'Last_Success_Date'  -- SUBSTRING参数错误
    , applied_policy_names
from resources_status
where 'Last_Success_Date' >= DATEADD(DAY, -2,GETDATE())*  -- 引用字符串别名、多余*号

错误分析 #

1. SUBSTRING参数错误：函数格式应为SUBSTRING(字段名, 起始位置, 长度)，原语句中SUBSTRING(last_success,actions,27,25)参数数量和逻辑错误，actions是另一个别名，不能作为参数传入。
2. WHERE子句引用错误：'Last_Success_Date'是字符串常量，不是字段别名，SQL Server不允许在WHERE中直接使用SELECT里的别名。
3. 类型未转换：提取的日期字符串未转换为datetime/datetimeoffset类型，无法和DATEADD返回的datetime类型直接比较。
4. 语法错误：WHERE子句末尾多余的*号。

解决思路与修正代码 #

步骤1：正确提取日期时间字符串 #

先确认last_success字段中日期时间的起始位置和长度，确保SUBSTRING能准确提取出2022-08-07T23:03:36-07:00格式的字符串。假设正确的提取逻辑是从第1位开始取25个字符（需根据实际字段内容调整）。

步骤2：转换为支持时区的日期类型 #

由于提取的日期带时区偏移，使用TRY_CONVERT(datetimeoffset, 提取的字符串)进行转换，避免转换失败报错。

步骤3：避免别名引用问题 #

使用CTE（公共表表达式）先处理字段提取和转换，再在WHERE中筛选；或者直接在WHERE子句中重复提取转换逻辑。

修正方案1：使用CTE

WITH ProcessedData AS (
    SELECT 
        resource_key,
        SUBSTRING(last_actions, 3, 13) AS Action,
        TRY_CONVERT(datetimeoffset, SUBSTRING(last_success, 1, 25)) AS Last_Success_Date,  -- 调整SUBSTRING参数为实际正确值
        applied_policy_names
    FROM resources_status
)
SELECT 
    resource_key,
    Action,
    Last_Success_Date,
    applied_policy_names
FROM ProcessedData
WHERE Last_Success_Date >= DATEADD(DAY, -2, SYSDATETIMEOFFSET())  -- 使用SYSDATETIMEOFFSET匹配时区类型

修正方案2：直接在WHERE中处理

SELECT 
    resource_key,
    SUBSTRING(last_actions, 3, 13) AS Action,
    TRY_CONVERT(datetimeoffset, SUBSTRING(last_success, 1, 25)) AS Last_Success_Date,
    applied_policy_names
FROM resources_status
WHERE TRY_CONVERT(datetimeoffset, SUBSTRING(last_success, 1, 25)) >= DATEADD(DAY, -2, SYSDATETIMEOFFSET())

额外说明 #

• 如果不需要时区信息，可以转换为datetime类型：TRY_CONVERT(datetime, SUBSTRING(last_success, 1, 19))（提取2022-08-07T23:03:36部分）
• 使用TRY_CONVERT而非CONVERT可以避免因部分数据格式错误导致整个查询失败，转换失败的记录会返回NULL，不会影响其他数据
• 务必根据last_success字段的实际内容调整SUBSTRING的起始位置和长度，确保提取的字符串格式正确

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