计算两个日期之间的经过时间

I have created a function that takes 2 dates and returns descriptive duration like this:

我创建了一个函数,它需要2个日期并返回描述性持续时间,如下所示:

1 Year 3 Months 2 Weeks 5 Days 10 Hours

1年3个月2周5天10个小时

The problem I am facing with this function is that if the duration is less than a month but if the two dates belong to different months, it returns month duration.

我使用此功能时遇到的问题是,如果持续时间少于一个月但如果两个日期属于不同月份,则返回月持续时间。

Can someone please help me tweak this function?

有人可以帮我调整这个功能吗?

You can find the query here:

你可以在这里找到查询:

http://data.stackexchange.com/*/query/edit/149306

As you can see I am passing start date as 2013-08-29 13:48:35.710 and end date as 2013-09-03 17:04:27.493 and that should have returned the duration as 5 days 3 hours 15 minutes but it returns 1 Month 3 Hours 15 minutes.

正如你所看到的那样,我的开始日期为2013-08-29 13:48:35.710,结束日期为2013-09-03 17:04:27.493,应该已经将持续时间恢复为5天3小时15分钟但是它返回1个月3个小时15分钟。

How do I tweak this in such a way that it displays the correct duration?

我如何调整它以显示正确的持续时间?

1 个解决方案

#1

DECLARE @date1 DATETIME, @date2 DATETIME
DECLARE @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @weeks BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT
DECLARE @KEEP DATETIME

SET @date1 = '20130829 13:48:35.710'
SET @date2 = '20130903 17:04:27.493'

if @date1>@date2 
begin
  SET @KEEP=@date1
  SET @date1=@date2
  SET @date2=@KEEP  
end

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 @weeks=DATEDIFF(wk,@date1,@date2)
if DateAdd(wk,-@weeks,@date2)<@date1 Select @weeks=@weeks-1
Set @date2= DateAdd(wk,-@weeks,@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)

Select @result= ISNULL(CAST(NULLIF(@years,0) as varchar(10)) + ' Years','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) as varchar(10)) + ' Months','')
     + ISNULL(' ' + CAST(NULLIF(@weeks,0) as varchar(10)) + ' Weeks','')     
     + 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','')

Select @result     

-- OUTPUT :  5 Days 3 Hours 16 Minutes