我的数据库A,想返回两个时间的时间差,两个时间的格式如下:
a b
11:00:00 12:20:11
然后我想返回的格式是
c
1:20:11
我用datediff,但出不了我的结果,格式都不是那样的,请问该怎么写?
我的回答:
SELECT
CAST
(
DATEDIFF
(HH,
'
11:00:00
'
,
'
12:20:11
'
)
AS
VARCHAR
)
+ ' : ' +
CAST (( DATEDIFF (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR )
+ ' : ' +
CAST (( DATEDIFF (SS, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR )
+ ' : ' +
CAST (( DATEDIFF (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR )
+ ' : ' +
CAST (( DATEDIFF (SS, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR )
如果返回格式要求是01:20:11,则代码如下:
SELECT
RIGHT
(
'
0
'
+
CAST
(
DATEDIFF
(HH,
'
11:00:00
'
,
'
12:20:11
'
)
AS
VARCHAR
),
2
)
+ ' : ' +
RIGHT ( ' 0 ' + CAST (( DATEDIFF (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR ), 2 )
+ ' : ' +
RIGHT ( ' 0 ' + CAST (( DATEDIFF (SS, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR ), 2 )
+ ' : ' +
RIGHT ( ' 0 ' + CAST (( DATEDIFF (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR ), 2 )
+ ' : ' +
RIGHT ( ' 0 ' + CAST (( DATEDIFF (SS, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) AS VARCHAR ), 2 )
答案参考自:how to display hh:mm format using DateDiff() function
感谢沐枫提供的更简单的解决方案:
select
convert
(
varchar
,
convert
(
datetime
,
datediff
(SS,
'
11:00:00
'
,
'
12:20:11
'
)
/
convert
(
decimal
,
86400
)),
8
)