如何编写SQL存储过程实现完整或部分时间（仅时分）的过滤功能

实现逻辑 #

过滤逻辑适配多种入参格式，仅对时间的小时、分钟维度做匹配，具体规则：

• 入参为HH:mm格式：同时匹配小时等于HH、分钟等于mm
• 入参为HH:格式：仅匹配小时等于HH
• 入参为无冒号的纯数字：匹配小时的字符串形式包含该数值，或分钟的字符串形式包含该数值

测试表初始化 #

先执行以下语句创建测试表并插入测试数据：

CREATE TABLE test 
(
    id INT,
    startTime DateTime
);

INSERT INTO test (id, startTime) VALUES (1, '2021-10-25 09:15:00');
INSERT INTO test (id, startTime) VALUES (2, '2021-10-25 10:15:00');
INSERT INTO test (id, startTime) VALUES (3, '2021-10-25 10:30:00');
INSERT INTO test (id, startTime) VALUES (4, '2021-10-25 11:30:00');
INSERT INTO test (id, startTime) VALUES (5, '2021-10-25 11:45:00');
INSERT INTO test (id, startTime) VALUES (6, '2021-10-25 13:45:00');
INSERT INTO test (id, startTime) VALUES (7, '2021-10-25 14:50:00');
INSERT INTO test (id, startTime) VALUES (8, '2021-10-25 15:51:00');
INSERT INTO test (id, startTime) VALUES (9, '2021-10-25 15:58:00');
INSERT INTO test (id, startTime) VALUES (10,'2021-10-25 16:10:00');

完整存储过程代码 #

CREATE PROCEDURE GetFilteredRecords
    @StartTimeFilter VARCHAR(5)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT id, startTime
    FROM test
    WHERE 
    -- 处理带冒号的入参场景
    (CHARINDEX(':', @StartTimeFilter) > 0 
        AND 
        (
            -- 场景1：入参为HH:mm格式，同时匹配小时+分钟
            (LEN(@StartTimeFilter) > CHARINDEX(':', @StartTimeFilter) 
                AND DATEPART(HOUR, startTime) = LEFT(@StartTimeFilter, CHARINDEX(':', @StartTimeFilter)-1)
                AND DATEPART(MINUTE, startTime) = RIGHT(@StartTimeFilter, LEN(@StartTimeFilter) - CHARINDEX(':', @StartTimeFilter))
            )
            OR
            -- 场景2：入参为HH:格式，仅匹配小时
            (LEN(@StartTimeFilter) = CHARINDEX(':', @StartTimeFilter)
                AND DATEPART(HOUR, startTime) = LEFT(@StartTimeFilter, CHARINDEX(':', @StartTimeFilter)-1)
            )
        )
    )
    OR
    -- 场景3：入参为无冒号纯数字，匹配小时或分钟包含该数值
    (CHARINDEX(':', @StartTimeFilter) = 0
        AND 
        (
            CHARINDEX(@StartTimeFilter, CAST(DATEPART(HOUR, startTime) AS VARCHAR(2))) > 0
            OR
            CHARINDEX(@StartTimeFilter, CAST(DATEPART(MINUTE, startTime) AS VARCHAR(2))) > 0
        )
    )
END
GO

调用测试示例 #

你可以通过以下语句验证过滤效果，结果完全符合需求示例：

• EXEC GetFilteredRecords '09:15'：返回id为1的记录
• EXEC GetFilteredRecords '10'：返回id为2、3、10的记录
• EXEC GetFilteredRecords '5'：返回id为1、2、5、6、7、8、9的记录
• EXEC GetFilteredRecords '45'：返回id为5、6的记录
• EXEC GetFilteredRecords '13:45'：返回id为6的记录
• EXEC GetFilteredRecords '11:'：返回id为4、5的记录

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