SQL Server中字符串如何转日期 修复UserLog表按日期删除的存储过程问题

• 字段函数运算导致索引失效：原语句对EnterDate字段使用TRY_CAST函数做转换，会导致该字段上的索引无法被查询优化器识别调用，数据量较大时会出现执行超时、语句未执行完成就中断的问题，最终没有完成数据清理。
• 转换失败导致数据漏删：如果EnterDate是字符串类型，或者存在格式不规范的日期值、NULL值，TRY_CAST转换后会返回NULL，NULL和日期参数比较的结果为未知，这些行不会被WHERE条件匹配，导致符合日期范围的部分数据没有被删除。
• 未做参数合法性校验：如果传入的@FromDate晚于@ToDate，WHERE条件永远不成立，不会删除任何数据。

场景1：EnterDate为日期时间类型（datetime/datetime2/smalldatetime） #

直接去掉字段上的转换函数，调整日期范围判断逻辑，既能命中索引，又能覆盖@ToDate当天所有带时间的记录：

CREATE PROCEDURE [dbo].[Sp_CleanUserLogTable]
    @FromDate date,
    @ToDate date
AS
BEGIN
    SET NOCOUNT ON;
    -- 校验参数合法性
    IF @FromDate > @ToDate
    BEGIN
        RAISERROR('起始日期不能晚于结束日期', 16, 1);
        RETURN;
    END

    DELETE FROM UserLog 
    WHERE EnterDate >= @FromDate 
      -- 小于结束日期+1天，覆盖结束日期当天所有时间的记录
      AND EnterDate < DATEADD(DAY, 1, @ToDate);
END

场景2：EnterDate为字符串类型（varchar/nvarchar） #

1. 先执行如下语句排查转换失败的异常数据：

SELECT * FROM UserLog WHERE TRY_CAST(EnterDate AS date) IS NULL;

如果存在转换失败的记录，需要先修正异常数据，或者根据实际存储的日期格式调用TRY_CONVERT指定样式转换，例如存储格式为日/月/年时使用样式代码103：

TRY_CONVERT(date, EnterDate, 103)

2. 调整后的存储过程如下：

CREATE PROCEDURE [dbo].[Sp_CleanUserLogTable]
    @FromDate date,
    @ToDate date
AS
BEGIN
    SET NOCOUNT ON;
    IF @FromDate > @ToDate
    BEGIN
        RAISERROR('起始日期不能晚于结束日期', 16, 1);
        RETURN;
    END

    DELETE FROM UserLog 
    WHERE TRY_CAST(EnterDate AS date) >= @FromDate 
      AND TRY_CAST(EnterDate AS date) <= @ToDate;
END

执行存储过程后可以通过SELECT @@ROWCOUNT查看本次实际删除的记录行数，验证是否符合预期。

内容的提问来源于stack exchange，提问作者Newzad K Mawatî