将 varchar 时间(时间> 24 小时)转换为浮点数

Question

我有一个列，其中的时间记录表示两个日期之间的差异。其中一些记录超过了24小时。

由于时间语法不考虑> 24小时的时间记录，我不得不将这些记录转换为varchar hh:mm:ss，如下面的链接中的解决方案所述:

SQL日期格式[h]:mm:ss像Excel一样，超过24小时

这个工作得很好，但是我的问题是我现在需要将这些转换为浮点数(例如69:00:00.0000作为2.875)。

不幸的是，我不能使用通常建议的datediff来做到这一点，因为对于超过24小时的记录，它会抛出"超出范围"的错误。

有什么好主意吗?

Answer 1

感谢这些有帮助的帖子，我采纳了原始评论的建议，因为计算日期之间的差异作为数字并稍后在表示层(Excel)中转换它要容易得多。

为此，我使用DATEPART逻辑将日、时、分、秒和在一起得出一个"十进制日"数字，然后使用Excel将其转换为[h]:mm:ss

(如。"2016-04-30 23:23:00"与"2016-04-30 23:25:00"相差2分钟。这是0.0013888833作为十进制日，在Excel中转换为0:02:00，格式为[h]:mm:ss)

遗憾的是，我没有时间测试替代解决方案是否提供工作。

Answer 2

像这样?

DECLARE @YourTime VARCHAR(100)='69:00:00.0000';
WITH Splitted AS
(
    SELECT @YourTime AS t
          ,CHARINDEX(':',@YourTime)-1 AS HourLength
          ,LEFT(@YourTime,CHARINDEX(':',@YourTime)-1) AS HourPart
)
SELECT HourPart/24 + CAST(CAST(CAST(STUFF(@YourTime,1,HourLength,HourPart-(HourPart/24)*24) AS TIME) AS DATETIME) AS FLOAT)
FROM Splitted

UPDATE与function

相同

CREATE FUNCTION dbo.ConvertExceedingTimeToFloat(@TimeString VARCHAR(100))
RETURNS FLOAT
AS
BEGIN
    DECLARE @RetVal FLOAT;
    WITH Splitted AS
    (
        SELECT @TimeString AS t
              ,CHARINDEX(':',@TimeString)-1 AS HourLength
              ,LEFT(@TimeString,CHARINDEX(':',@TimeString)-1) AS HourPart
    )
    SELECT @RetVal = HourPart/24 + CAST(CAST(CAST(STUFF(@TimeString,1,HourLength,HourPart-(HourPart/24)*24) AS TIME) AS DATETIME) AS FLOAT)
    FROM Splitted;
    RETURN @RetVal;
END
GO
SELECT dbo.ConvertExceedingTimeToFloat('69:00:00.0000')
GO
DROP FUNCTION dbo.ConvertExceedingTimeToFloat;

Answer 3

也许您可以将:替换为.。这将给你一个可以用PARSENAME解析的字符串(从SQL Server 2012开始)，然后CAST为float:

DECLARE @SomeTime VARCHAR(100)='69:00:00.0000'
SELECT CAST(PARSENAME(REPLACE(@SomeTime,':','.'),4)/24.00 as float)

或CHARINDEX:

的子字符串

SELECT CAST(SUBSTRING(@SomeTime,1,CHARINDEX(':',@SomeTime)-1)/24.00 as float)

输出:

2,875

希望我答对了你的问题。

编辑

以上方法只使用了几个小时。如果你需要更深入:

DECLARE @SomeTime VARCHAR(100)='69:55:10.999',
        @x xml
--Convert to XML
SELECT @x = CAST('<p>'+REPLACE(REPLACE(@SomeTime,':','.'),'.','</p><p>') +'</p>' as xml)
--Working with XML, each part need convertion
SELECT  CAST(
        t.c.value('/p[1]','int')/24.00+
        (t.c.value('/p[2]','int')/60.00)/24.00+
        ((t.c.value('/p[3]','int')/60.00)/60.00)/24.00+
        (((t.c.value('/p[4]','int')/1000.00)/60.00)/60.00)/24.00
        as float) result
FROM @x.nodes('/') as t(c)

输出:

2,9133217194375

注意:

我对SQL Server中的MATH了解不多，请报告/建议，如果您在当前解决方案中看到任何流