如何将十进制时间转换为DateTime，以便我可以在其上运行DateAdd计算

Question

我有一个MSSQL表，其中原始数据的格式化为：

date1                time1
2008-01-20 00:00:00   654
2008-01-20 00:00:00   659
2008-01-20 00:00:00   1759

我需要加入其中两个，这样我就可以查询过去15个小时内发生的所有日期_time。我所做的是

在选择语句中：

combined = CONVERT(VARCHAR(10), Date1, 103) +' ' + (left((replace((CONVERT(dec(7, 2), time1) / 100 ),'.',':')),4) + ':00') ,

这有助于我获得

的结果

date1                time1  combined1
2008-01-20 00:00:00   654  20/01/2008 6:54:00
2008-01-20 00:00:00   659  20/01/2008 6:59:00
2008-01-20 00:00:00   1759 20/01/2008 17:5:00

i可以 t change the table data & I can t获得正确的语法以完全转换它（例如，考虑24h小时格式-1759）

最后，我需要能够在合并的1列上进行陈述，才能看到最近15个小时内发生的行

DATEADD(hour, - 15, GETDATE())

预先感谢

Answer 1

尝试此

select date1,time1, DATEADD(MINUTE, time1%100, DATEADD(HOUR, time1/100, convert(varchar(10),date1,101))) as Combined 
from Table
Where DATEADD(MINUTE, time1%100, DATEADD(HOUR, time1/100, convert(varchar(10),date1,101)))>(DATEADD(hour,-15,GETDATE()))

Answer 2

尝试这样：

DECLARE @date DATETIME = '2008-01-20 00:00:00'
,       @Time INT = 654
SELECT DATEADD(MINUTE, @Time%100, DATEADD(HOUR, @Time/100, @date))

Answer 3

;WITH cte
AS (SELECT CAST('2008-01-20 00:00:00' AS DATETIME) AS date1,   654 AS Time1
    UNION ALL
    SELECT CAST('2008-01-20 00:00:00' AS DATETIME) AS date1,   659 AS Time1
    UNION ALL
    SELECT CAST('2008-01-20 00:00:00' AS DATETIME) AS date1,  1759 AS Time1
)
SELECT 
    DATEADD(ms, DATEDIFF(ms, '00:00:00', CAST(FORMAT(Time1, '##:##') AS TIME)),  date1) AS [CombinedDateTime]
FROM cte;
--Results to: 
CombinedDateTime
2008-01-20 06:54:00.000
2008-01-20 06:59:00.000
2008-01-20 17:59:00.000