如何处理类似于DATEDIFF(MINUTE, '00:00', '24:20')的情况？

Question

我的表中有一列，我们以'HH:MM'的格式存储字符串值。在从这个表中获取记录时，一切工作都正常。

DATEDIFF(MINUTE, '00:00', ColumnName)

问题出现在当我们的值大于 23:59 时。

会显示以下错误：

将日期和/或时间从字符字符串转换失败。

有没有人能为我提供实现这种情况的正确方法。

- Ashish Bisht

---

正确的方法是使用适当的数据类型。 - Dan Bracuk

实际上，我们需要保存大于“23:59”的值，因此我们将数据类型设置为Varchar。 - Ashish Bisht

类似于 '24:20' 这样的东西不能直接转换为 TIME 或 DateTime。最好的方法可能是使用 : 进行分割，然后将小时乘以60再加上分钟。 - ughai

SQL Server没有表示时间跨度的数据类型（它只有表示一天中某个时间的类型）。因此，最自然的表示这种数据的方式可能是将其表示为分钟数的int数字（如果需要以分钟为粒度）。将其转换为hh:mm 字符串（或从这些字符串进行转换）作为用户界面(UI)的问题，而不是数据库(DB)的问题。 - Damien_The_Unbeliever

“24:20” 实际上代表什么？这不是一个有效的时间。 - Dan Bracuk

Answer 1

如果您存储的值不是时间格式，为什么不直接将分钟数存储，并在输出时转换为所需格式呢？

否则，我建议您将该值转换为分钟数：

select (cast(left(ColumnName, 2) as int) * 60 +
        cast(right(ColumnName, 2) as int)
       ) as Minutes

如果您不使用日期/时间值，则没有必要使用专门为它们设计的函数。

编辑：

如果要处理超过99小时的时间，请使用charindex()函数：

select (cast(left(ColumnName, charindex(':', ColumnName) - 1) as int) * 60 +
        cast(right(ColumnName, 2) as int)
       ) as Minutes

- Gordon Linoff

---

当出现类似于“100:00”的值时，它会失败。 - Ashish Bisht

@AshishBisht . . . 看一下修改后的代码。现在应该可以工作了。 - Gordon Linoff

Answer 2

看起来你保存的是一个时间段的长度。尝试将其存储为分钟。由于查询基于冒号，所以它可以处理不同长度的数字。

DECLARE @yourTable TABLE (ColumnName VARCHAR(10));
INSERT INTO @yourTable
VALUES  ('100:00'),
        ('24:20');

SELECT  ColumnName,
        (hr * 60) + minut AS time_period_in_minutes
FROM @yourTable
CROSS APPLY (SELECT CAST(SUBSTRING(ColumnName,0,CHARINDEX(':',ColumnName)) AS INT),
                    CAST(SUBSTRING(ColumnName,CHARINDEX(':',ColumnName) + 1,LEN(ColumnName)) AS INT)) CA(hr,minut)

结果：

ColumnName time_period_in_minutes
---------- ----------------------
100:00     6000
24:20      1460

- Stephan

Answer 3

试一下这个

select DATEDIFF(MINUTE, '00:00', case when ISDATE(ColumnName)=0 then '00:00' else ColumnName end )

- Mukesh Kalgude