如何找到两个日期之间的持续时间 [英] 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屋！