suds suds - 2 months ago 4x
SQL Question

how to find the duration between two dates

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

Result which needed



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 @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)

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.