MSSQL轉換日期datetime 時發生算術溢位錯誤。

mssql sql datetime

rain_yu 2023-07-12 09:53:24 ‧ 1647 瀏覽

分享至

各位大老好，我現在是在統計使用的表單每個流程簽核的時間，
看哪一關的花的時間最多，
FlowName是各流程名稱，count是簽核次數，time是由start與endtime的差值。
processtime是本次的主要問題。
"
請問一下各位大老，如何讓time的欄位轉換成Processtime欄位內容，
但是超過24H要顯示天數，天數：小時：分鐘：秒這樣子顯示。
目前看來這個轉換方式超過24h就不會記錄。
"
我的time欄位是varchar，所以要一直轉換。

SELECT t1.[ITEM34] AS FlowName,
       COUNT(t1.[insid]) AS count,
       ABS(CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) - SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) AS processtime_positive,
       CONVERT(varchar, DATEADD(ms, (CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) AS BIGINT) - CAST(SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) % 1000, 
        DATEADD(s, (CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) AS BIGINT) - CAST(SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) / 1000, '19700101')), 114) AS processtime
FROM [flowring].[dbo].[ART05551649744774878_Log] t1
JOIN [flowring].[dbo].[Task] t2 ON t1.[TskID] = t2.[TskID]
JOIN [flowring].[dbo].[XX_PR_SortFlow] t3 ON t1.[ITEM34] = t3.[FlowName]
WHERE t1.item3 = 'false' AND t1.item6 BETWEEN '2022/01/01' AND '2022/12/01'
GROUP BY t1.[ITEM34], t3.[FlowName], t3.[SortId]
ORDER BY t3.[SortId]

目前輸出結果如下

有嘗試使用DATEDIFF，會有錯誤訊息，這個我有丟給GPT問過，但是沒有給我正確答案。
訊息 8115，層級 16，狀態 2，行 1
轉換 expression 到資料類型 datetime 時發生算術溢位錯誤。

SELECT t1.[ITEM34] AS FlowName,
       COUNT(t1.[insid]) AS count,
       ABS(CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) - SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) AS processtime_positive,
       CONVERT(varchar, DATEADD(ms, (CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) AS BIGINT) - CAST(SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) % 1000, 
        DATEADD(s, (CAST(SUM(CAST(t2.[EndTime] AS BIGINT)) AS BIGINT) - CAST(SUM(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT)) / 1000, '1970-01-01')), 114) AS processtime,
       DATEDIFF(DAY, CAST(MIN(CAST(t2.[StartTime] AS BIGINT)) AS BIGINT), CAST(MAX(CAST(t2.[EndTime] AS BIGINT)) AS BIGINT)) AS processtime_days
FROM [flowring].[dbo].[ART05551649744774878_Log] t1
JOIN [flowring].[dbo].[Task] t2 ON t1.[TskID] = t2.[TskID]
JOIN [flowring].[dbo].[XX_PR_SortFlow] t3 ON t1.[ITEM34] = t3.[FlowName]
WHERE t1.item3 = 'false' 
GROUP BY t1.[ITEM34], t3.[FlowName], t3.[SortId]
ORDER BY t3.[SortId]

Nick iT邦新手 5 級 ‧ 2023-07-14 09:28:43 檢舉

你發問的方式值得學習. 感恩

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

shing_pascal

iT邦新手 2 級 ‧ 2023-07-12 11:44:00

最佳解答

要先把TIME轉換成數字，
然後用以下公式，會得到字串，就是你想要的結果.有修改一下！
把備註拿掉了。
4203481776 是你提供的第一筆資料的TIME 數字

CONVERT(VARCHAR(10),CONVERT(INT,ROUND((4203481776/86400000),0,1))) + ':'+ CONVERT(VARCHAR(10),CONVERT(INT,ROUND((4203481776%86400000)/3600000,0,1))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,ROUND((4203481776%3600000)/60000,0,1))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,ROUND((4203481776%60000)/1000,0,1))) + ':' + CONVERT(VARCHAR(10),CONVERT(INT,ROUND((4203481776%1000),0,1))) --微秒

回應 1
分享
檢舉

rain_yu iT邦研究生 5 級 ‧ 2023-07-13 08:19:34 檢舉

感謝回答

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2023-07-12 10:05:15

請參考 I. 求得 startdate 與 enddate 的差距，並以日期部分字串表示

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
    @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1 
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1 
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1 
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1 
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1 
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1 
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
     + CASE
            WHEN @milliseconds > 0
                THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
            ELSE ''
       END 
     + ' seconds','')

SELECT @result

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

回應 1
分享
檢舉

rain_yu iT邦研究生 5 級 ‧ 2023-07-13 08:19:42 檢舉

感謝回答

登入發表回應

alien663

iT邦研究生 4 級 ‧ 2023-07-12 11:09:57

直接算前後的秒數差異，然後用Function直接給出你要的結果。
會有Overflow的問題，我猜是有些資料的時間欄位為空白，計算時間差距時會自動變成1900-01-01去做計算，然後算出來的數字過大。

create function fn_SecondesFormatDateTime(@seconds BIGINT)
returns varchar(32)
as
begin
    declare @result varchar(64) = ''
	if(@seconds > 86400)begin
		set @result += concat(@seconds/86400, ' Days ' )
		set @seconds = @seconds % 86400
	end
	if(@seconds > 3600)begin
		set @result += concat(@seconds/3600, ' Hours ')
		set @seconds = @seconds % 3600
	end
	if(@seconds > 60) begin
		set @result += concat(@seconds/60, ' Minutes ')
		set @seconds = @seconds % 60
	end
	set @result += concat(@seconds, ' Seconds ')
	return @result
end
go

select dbo.fn_SecondesFormatDateTime(DATEDIFF(s, '20230405', '20230630'))