sql计算两个时间之间的差，并用时分秒表示

最新推荐文章于 2024-01-31 16:47:53 发布

weixin_30920513

最新推荐文章于 2024-01-31 16:47:53 发布

阅读量725

点赞数 1

原文链接：http://www.cnblogs.com/tianxiaotian/p/10277141.html

版权

这是自己写的方法，总觉得会有更好的办法实现这个效果呢？

SELECT 
case when CONVERT(nvarchar,DATEDIFF(SECOND,FinishAt,getdate() ))>86400  then
CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )/86400)+'天'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%86400/3600)+'时'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%86400%3600/60)+'分'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%86400%3600%60)+'秒'
when CONVERT(nvarchar,DATEDIFF(SECOND,FinishAt,getdate() ))>3600 then 
CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )/3600)+'时'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%3600/60)+'分'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%3600%60)+'秒'
when  CONVERT(nvarchar,DATEDIFF(SECOND,FinishAt,getdate() ))>60 then
CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )/60)+'分'+CONVERT(nvarchar, DATEDIFF(SECOND,FinishAt,getdate() )%60)+'秒'
else CONVERT(nvarchar,DATEDIFF(SECOND,FinishAt,getdate() ))+'秒' end TimeSpend   
  FROM [BPMDB].[dbo].[BPMInstTasks] A