需要计算截止日期的年、月和日

I have a table called dates,

我有一张叫date的表格，

Opendate    |   Closedate   
------------+---------------
2015-07-09  |   2016-08-10

I am expecting the output like,

我希望输出是，

opendate    |   closedate   |   diff    
------------+---------------+----------------------
2015-07-09  |   2016-08-10  |   1year 1month 1day   
2015-07-09  |   2016-03-01  |   8 months 20 days
2015-07-09  |   2015-07-11  |   2 days

But when I run this query:

但是当我运行这个查询时:

SELECT opendate, 
       closedate, 
       Datediff(year, opendate, closedate)  AS years, 
       Datediff(month, opendate, closedate) AS months, 
       Datediff(day, opendate, closedate)   AS days 
FROM   dates

It is giving me an output like,

它给出的输出是，

opendate    |   closedate   | years | months |  days    
------------+---------------+-------+--------+---------
2015-07-09  |   2016-08-10  |   1   |   13   |  397

How can we calculate 1 year 1 month and 1 day

如何计算1年1个月1天

3 个解决方案

#1

You can use Stacked CTE to find one by one the next year, month and date.

你可以使用堆叠CTE在明年，月和日之间找到一个。

Explanation

解释

Query Below first finds out the DATEDIFF Years of opendate and closedate and checks if the resulting date is greater than closedate. if it is, the actual year difference is DATEDIFF of Y -1. use this new date and fetch the DATEDIFF of months using the same logic and then get the difference in days.

下面的查询首先查找opendate和closedate的DATEDIFF年，并检查结果日期是否大于closedate。如果是，实际的年差是Y -1的日期。使用这个新的日期并使用相同的逻辑获取几个月的数据，然后在几天内得到不同的结果。

Online Example

网上的例子

Query

查询

WITH D(Opendate,Closedate)AS
(
SELECT CAST('2015-07-09' AS DATE),CAST('2016-08-10' AS DATE)
UNION ALL 
SELECT CAST('2015-07-09' AS DATE),CAST('2016-03-01' AS DATE)
UNION ALL 
SELECT CAST('2015-07-09' AS DATE),CAST('2015-07-11' AS DATE)

),Y AS
(
SELECT Opendate,Closedate,
  CASE 
  WHEN DATEADD(YEAR,DATEDIFF(YEAR,Opendate,Closedate),Opendate) > Closedate 
  THEN DATEDIFF(YEAR,Opendate,Closedate) - 1 
  ELSE DATEDIFF(YEAR,Opendate,Closedate)
  END Years
FROM D
), YDate as 
(
SELECT Opendate,Closedate,Years,DATEADD(YEAR,Years,Opendate) as Newopendate
FROM Y
),M AS
(
SELECT Opendate,Closedate,Years,Newopendate,
CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH,Newopendate,Closedate),Newopendate) > Closedate 
THEN DATEDIFF(MONTH,Newopendate,Closedate) - 1 
ELSE DATEDIFF(MONTH,Newopendate,Closedate) 
END Months
FROM YDate
) 
SELECT Opendate,Closedate,Years,Months,DATEDIFF(Day,DATEADD(MONTH,Months,Newopendate),Closedate) as days
FROM M

Result

结果

Opendate    Closedate   Years   Months  days
09-07-2015 00:00    10-08-2016 00:00    1   1   1
09-07-2015 00:00    01-03-2016 00:00    0   7   21
09-07-2015 00:00    11-07-2015 00:00    0   0   2

#2

SELECT opendate, 
       closedate, 
       ( ( Datediff(year, opendate, closedate) +  'years' )+  
       (( Datediff(month, opendate, closedate) - 
             12 * Datediff(year, opendate, closedate)) + 'months') +
        ( Datediff(day, opendate, closedate) - 
         ( Datediff(year, opendate, closedate) * 365 - 
         (Datediff(month, opendate, closedate) * 12) )) + 'days'

FROM   dates

The logic is you concatenate the years and then deduct the no of months of a year. Similarly deduct for days as well

逻辑是将年份连接起来，然后减去一年中的月份。同样也要扣除几天

#3

Create one function as Below

创建一个如下所示的函数

 CREATE FUNCTION dbo.GetYearMonthDays
    (
       @FromDate DATETIME
    )
    RETURNS NVARCHAR(100)
    AS
    BEGIN
       DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
       SELECT @date =@FromDate

    SELECT @tmpdate = @date

    SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
    SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
    SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

        RETURN  CONVERT(varchar(10), @years) +' Years   ' +  CONVERT(varchar(10), @months)  + ' Month  ' + CONVERT(varchar(10), @days) + ' Days'
    END
    GO

And use is as below

使用如下。

SELECT opendate, 
       closedate,dbo.GetYearMonthDays(closedate)
FROM   dates

This will give you what you wants.

这会给你想要的。

#1