将nvarchar格式自定义时长转换后用于SQL查询筛选

核心思路 #

把nvarchar类型的Total_Time（格式为d:hh:mm）转换成总分钟数（或总秒数），将时长的比较转化为数值比较，就能直接用</>等运算符完成筛选。

直接查询的实现 #

基础版本（数据格式完全规范时使用） #

通过REPLACE把冒号替换为点，再用PARSENAME拆分出天、小时、分钟，计算总分钟数后和目标时长的总分钟数对比：

SELECT * 
FROM [TestDB].[dbo].[myData]
WHERE 
    -- 计算Total_Time对应的总分钟数
    (CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 3) AS INT) * 1440) 
    + (CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 2) AS INT) * 60) 
    + CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 1) AS INT) 
    < 80; -- 0天1小时20分钟的总分钟数：0*1440 +1*60 +20 = 80

健壮版本（兼容格式异常数据） #

如果存在格式不规范的记录（比如非数字、缺少部分字段），用TRY_CAST替代CAST避免查询报错，同时用ISNULL处理NULL值：

SELECT * 
FROM [TestDB].[dbo].[myData]
WHERE 
    ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 3) AS INT), 0) * 1440 
    + ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 2) AS INT), 0) * 60 
    + ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 1) AS INT), 0) 
    < 80;

优化方案（适合频繁查询场景） #

如果需要经常对该字段做筛选，建议新增持久化计算列，避免每次查询重复计算，大幅提升效率：

1. 添加计算列：

ALTER TABLE [TestDB].[dbo].[myData]
ADD Total_Time_Minutes AS 
    ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 3) AS INT), 0) * 1440 
    + ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 2) AS INT), 0) * 60 
    + ISNULL(TRY_CAST(PARSENAME(REPLACE(Total_Time, ':', '.'), 1) AS INT), 0)
PERSISTED;

2. 后续查询直接使用计算列：

SELECT * 
FROM [TestDB].[dbo].[myData]
WHERE Total_Time_Minutes < 80;

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