计算SQL Server中的时间差(以分钟为单位)

我需要两分钟之间的时差.我有如下所示的开始时间和结束时间：

start time | End Time    
11:15:00   | 13:15:00    
10:45:00   | 18:59:00

我需要第一行的输出为45,60,15,分别对应于11:15和12:00,12：00和13：00,13：00和13:15之间的时差.

解决方法

SELECT  Diff = CASE DATEDIFF(HOUR,StartTime,EndTime)
                    WHEN 0 THEN CAST(DATEDIFF(MINUTE,EndTime) AS VARCHAR(10))
                    ELSE CAST(60 - DATEPART(MINUTE,StartTime) AS VARCHAR(10)) +
                        REPLICATE(',60',DATEDIFF(HOUR,EndTime) - 1) + 
                        + ',' + CAST(DATEPART(MINUTE,EndTime) AS VARCHAR(10))
                END
FROM    (VALUES 
            (CAST('11:15' AS TIME),CAST('13:15' AS TIME)),(CAST('10:45' AS TIME),CAST('18:59' AS TIME)),CAST('11:59' AS TIME))
        ) t (StartTime,EndTime);

要获得24列,您可以使用24个案例表达式,例如：

SELECT  [0] = CASE WHEN DATEDIFF(HOUR,EndTime) = 0
                        THEN DATEDIFF(MINUTE,EndTime)
                    ELSE 60 - DATEPART(MINUTE,StartTime)
                END,[1] = CASE WHEN DATEDIFF(HOUR,EndTime) = 1 
                        THEN DATEPART(MINUTE,EndTime)
                    WHEN DATEDIFF(HOUR,EndTime) > 1 THEN 60
                END,[2] = CASE WHEN DATEDIFF(HOUR,EndTime) = 2
                        THEN DATEPART(MINUTE,EndTime) > 2 THEN 60
                END -- ETC
FROM    (VALUES 
            (CAST('11:15' AS TIME),EndTime);

以下内容也有效,并且最终可能会比重复使用相同的case表达式更短：

WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY t1.N) - 1
    FROM    (VALUES (1),(1),(1)) AS t1 (N)
            CROSS JOIN (VALUES (1),(1)) AS t2 (N)
),YourData AS
(   SELECT  StartTime,EndTime
    FROM    (VALUES 
                (CAST('11:15' AS TIME),(CAST('09:45' AS TIME),CAST('11:59' AS TIME))
            ) AS t (StartTime,EndTime)
),PivotData AS
(   SELECT  t.StartTime,t.EndTime,n.Number,MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR,EndTime) = 0 THEN DATEDIFF(MINUTE,EndTime)
                                WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE,StartTime)
                                WHEN DATEDIFF(HOUR,t.StartTime,t.EndTime) <= n.Number THEN DATEPART(MINUTE,EndTime)
                                ELSE 60
                            END
    FROM    YourData AS t
            INNER JOIN Numbers AS n
                ON n.Number <= DATEDIFF(HOUR,EndTime)
)
SELECT  *
FROM    PivotData AS d
        PIVOT 
        (   MAX(MinuteDiff)
            FOR Number IN 
            (   [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
            ) 
        ) AS pvt;

它通过连接到24个数字的表来工作,因此不需要重复case表达式,然后使用PIVOT将这24个数字滚动到列中

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!