如何查找两个日期之间的持续时间 [英] how to find the duration between two dates

问题描述

我想查找两个日期列之间的持续时间。为此，我使用DATEDIFF函数分别查找号码年份和月份，但希望两个结果都列在单列中。
下面给出两列。

I want to find the duration between the two date columns. For this i used the DATEDIFF function to find number years and months separately but wanted both results in single column. The two columns are given below.

start_dt      |    end_dt
06-Oct-2009      15-Jul-2011  

需要的结果

Duration(years.months)
2.3

推荐答案

我认为没有现成的API以您提到的格式提供结果。您需要使用 DATEDIFF 函数来获取所需最小面额的差额，然后将结果与适当的值进行分隔，以获得所需格式的持续时间。如下所示：

I think there is no out-of-the-box API to provide the result in the format you mentioned. You need to use the DATEDIFF function to get the difference in the least denomination you need and then divide the result with appropriate value to get the duration in the format required. Something like this:

DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @duration INT

SELECT @start = '2009-10-06', @end = '2011-07-15'
SELECT @duration = DATEDIFF(mm, @start, @end)
SELECT CONVERT(NVARCHAR, @duration / 12) + '.' + CONVERT(NVARCHAR, @duration % 12)

通过编写一个将采用日期和最小面额并以所需格式返回持续时间的函数可以更好地实现，因为它将需要TSQL和纯SQL不够。

This can be better achieved by writing a function that would take the dates and least denomination and returns the duration in the format needed, as it would require TSQL and plain SQL wouldn't suffice.

这篇关于如何查找两个日期之间的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！