SQL Server:从字符串超过中午12点的字符转换日期和/或时间时，转换失败

Question

在过去的几天里，我的代码一直有效，但今天它出现了以下错误：

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

这是我的代码：

DECLARE @run_time INT
DECLARE @run_duration INT 
SET @run_time = '120609'
SET @run_duration = '2600'
SELECT
h.step_id,
CAST(j.[name] AS VARCHAR) as JobName,
h.step_name,
CAST(REPLACE(LEFT(CAST(case 
when len(@run_time) = 1 then '00:00:0' + cast(@run_time as varchar)
when len(@run_time) = 2 then '00:00:' + cast(@run_time as varchar)
when len(@run_time) = 3 then '00:0' + cast(left(@run_time,1) as varchar) + ':' + cast(right(@run_time,2) as varchar)
when len(@run_time) = 4 then '00:' + cast(left(@run_time,2) as varchar) + ':' + cast(right(@run_time,2) as varchar)
when len(@run_time) = 5 then '0' + cast(left(@run_time,1) as varchar) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR),    2, 2) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 4,2)
when len(@run_time) = 6 then cast(left(@run_time,2) as varchar) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 2,2) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 4,2)
END AS TIME), 8), ':', '') AS INT) StartTime

我发现错误唯一出现的时间是运行时间超过120000(下午12点(，因此我以前从未注意到它，因为代理作业在凌晨3点运行。这不应该是未来的问题，但以防万一，我想解决这个问题。我在任何地方都找不到与我的代码相似的示例。不幸的是，像这样投射结果是我在SSRS中获得图形的唯一方法。(INT>VARCHAR>TIME>INT(。

编辑-这里是一个更好的例子，我的代码使用了一个建议的答案：

SELECT
h.step_id,
CAST(j.[name] AS VARCHAR) as JobName,
h.step_name,
CAST(stuff(stuff(right('0000000' + h.run_time, 6), 5, 0, ':'), 3, 0, ':') as time)
FROM
msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 

谢谢，

Answer 1

如果你试图从HHMMSS格式的字符串中计算开始时间，那么这个方法对我来说似乎简单得多：

select cast(stuff(stuff(right('0000000' + str, 6), 5, 0, ':'), 3, 0, ':') as time)
from (values ('120609'), ('0'), ('1001')) v(str)

Answer 2

在您的情况下，问题是您试图转换的字符串："12:20:60"转换为TIME类型失败。

DECLARE @run_time INT
DECLARE @run_duration INT 
SET @run_time = '120609'
SET @run_duration = '2600'

SELECT
-- h.step_id,
--CAST(j.[name] AS VARCHAR) as JobName,
-- h.step_name,
--CAST(REPLACE(LEFT(CAST(
case 
when len(@run_time) = 1 then '00:00:0' + cast(@run_time as varchar)
when len(@run_time) = 2 then '00:00:' + cast(@run_time as varchar)
when len(@run_time) = 3 then '00:0' + cast(left(@run_time,1) as varchar) + ':' + cast(right(@run_time,2) as varchar)
when len(@run_time) = 4 then '00:' + cast(left(@run_time,2) as varchar) + ':' + cast(right(@run_time,2) as varchar)
when len(@run_time) = 5 then '0' + cast(left(@run_time,1) as varchar) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 2,2) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 4,2)
when len(@run_time) = 6 then cast(left(@run_time,2) as varchar) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 2,2) + ':' + SUBSTRING(CAST(@run_time AS VARCHAR), 4,2)
END ,
SUBSTRING(CAST(@run_time AS VARCHAR), 4,2)
--AS TIME)
--,8), ':', '') AS INT) StartTime

你检查过了吗？如何在SQL Server 2008中将整数(时间(转换为HH:MM:SS:：00？

最好的解决方案应该是Gordon 提出的解决方案

Answer 3

这对我有效：

SELECT
h.step_id,
CAST(j.[name] AS VARCHAR) as JobName,
h.step_name,
-- CAST(stuff(stuff(right('0000000' + h.run_time, 6), 5, 0, ':'), 3, 0, ':') as time)
(h.run_time / 1000000) % 100 as hour,
(h.run_time / 10000) % 100 as minute,
(h.run_time / 100) % 100 as second,
(h.run_time % 100) * 10 as millisecond,
dateadd(hour, (h.run_time / 1000000) % 100, dateadd(minute, (h.run_time / 10000) % 100, dateadd(second, (h.run_time / 100) % 100, dateadd(millisecond, (h.run_time % 100) * 10, cast('00:00:00' as time(2)))))) 
FROM
msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 

顺便说一句，CAST驱动器对我来说是错误的，还有

如果您不想使用select语句：

DECLARE @run_time INT
DECLARE @time_result TIME
SET @run_time = '120609'
SET @time_result = dateadd(hour, (@run_time / 1000000) % 100, dateadd(minute, (@run_time / 10000) % 100, dateadd(second, (@run_time / 100) % 100, dateadd(millisecond, (@run_time % 100) * 10, cast('00:00:00' as time(2)))))) 
select @time_result