问

如何在SQL Server中获取两个日期的时差

小丸子2502856277_253 发布于 2023-02-09 20:38

datetime

我试图通过以下代码查询两个日期之间的时间差:

DECLARE @firstDate DATETIME
DECLARE @SecondDate DATETIME

SELECT 
    CONVERT(VARCHAR(5), DATEDIFF(dd, @firstDate, @SecondDate)) + ' Days ' + 
    CONVERT(VARCHAR(5), (DATEDIFF(hh, @firstDate, @SecondDate) - DATEDIFF(dd, @firstDate, @SecondDate) * 24) % 3600) + ':' + 
    CONVERT(VARCHAR(5), DATEDIFF(s, @firstDate, @SecondDate) % 3600 / 60)  + ':' + 
    CONVERT(VARCHAR(5), DATEDIFF(s, @firstDate, @SecondDate) % 60) AS Time_remain_In_Days

但它会给出有问题的输出

set @firstDate = '2013-12-17 15:35'
set @SecondDate = '2013-12-18 14:35'

它将输出提供为1天-1:0:0

但它应该是0天23:0:0

我已经在视图中编写了上面的代码.

请帮我修改上面的查询以获得所需的输出

1 个回答

如果您使用的是SQL Server 2008或更高版本,则应该这样做:

declare @firstDate datetime
declare @SecondDate datetime
set @firstDate='2013-12-17T15:35:00'
set @SecondDate='2013-12-18T14:35:00'

select
    CONVERT(varchar(10),DATEDIFF(second,@firstDate,@secondDate)/86400)
        + ' days ' +
    CONVERT(varchar(10),CONVERT(time,
        DATEADD(second,DATEDIFF(second,@firstDate,@secondDate)%86400,0)))

对于早期版本,这应该做:

select
    CONVERT(varchar(10),DATEDIFF(second,@firstDate,@secondDate)/86400)
        + ' days ' +
    CONVERT(varchar(10),
        DATEADD(second,DATEDIFF(second,@firstDate,@secondDate)%86400,0),108)

2023-02-09 20:42 回答

mobiledu2502910077

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签