SQL中得到两个日期之间相隔 xx天xx小时xx分xx秒

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

gulingeagle

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

阅读量4k

点赞数

分类专栏：数据库文章标签： sql sql server

本文链接：https://blog.csdn.net/gulingeagle/article/details/4829017

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

该方法简单有效，原创。

select   cast( datediff( ss, '2021-5-30 11:00', GETDATE() ) / 86400 as varchar) +'天'+
               cast( datediff( ss, '2021-5-30 11:00', GETDATE() ) % 86400 / 3600 as varchar) +'小时'+
               cast( datediff( ss, '2021-5-30 11:00', GETDATE() ) % 3600 / 60 as varchar) +'分'+
               cast( datediff( ss, '2021-5-30 11:00', GETDATE() ) % 60 as varchar) + '秒'

==========以下是转载===========

SQL SERVER中可以这么做：

DECLARE @dt1 AS datetime, @dt2 AS datetime;

SELECT @dt1 = '2008-8-4 9:36:41', @dt2 = '2008-8-2 9:33:39';

DECLARE @Days AS int, @Hours AS int, @Minutes AS int, @Seconds AS int;

SET @Seconds = DATEDIFF( ss, @dt2, @dt1);

SET @Days = @Seconds / (24 * 60 * 60)

SET @Seconds = @Seconds - @Days * 24 * 60 * 60

SET @Hours = @Seconds / (60 * 60);

SET @Seconds = @Seconds - @Hours * 60 * 60

SET @Minutes = @Seconds / 60;

SET @Seconds = @Seconds - @Minutes * 60;

SELECT CONVERT(varchar(10), @Days ) + '天'
          + CONVERT(varchar(10), @Hours ) + '小时'
          + CONVERT(varchar(10), @Minutes ) + '分'
          + CONVERT(varchar(10), @Seconds ) + '秒';