how to find the duration between two dates
Solution 1
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)
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.
Solution 2
try something like this
SELECT
DATEDIFF( mm, start_dt, end_dt) / 12 AS years
, DATEDIFF( mm, start_dt, end_dt) % 12 AS months
Solution 3
QUERY :
SELECT
CONVERT(VARCHAR, DATEADD(month, 1, GETDATE()), 103) AS FinalDay,
CONVERT(VARCHAR, HRM_EmpGenDtls.DOJ, 106) AS DOJ,
CONVERT(varchar, DATEDIFF(year, HRM_EmpGenDtls.DOJ, GETDATE())) + ' Years' + ' ' +
CONVERT(varchar, DATEDIFF(month, HRM_EmpGenDtls.DOJ, GETDATE()) - (datediff(year, HRM_EmpGenDtls.DOJ, GETDATE()) * 12)) + ' Months' + ' ' + CONVERT(varchar, datepart(d,GETDATE()) - datepart(d, HRM_EmpGenDtls.DOJ)) + ' Days' AS ServicePeriod
FROM HRM_EmpGenDtls
That gives following answer :
FinalDay DOJ ServicePeriod
10/10/2013 03 Sep 2012 1 Years 0 Months 7 Days
10/10/2013 01 Jan 2000 13 Years 8 Months 9 Days
10/10/2013 22 Aug 2000 13 Years 1 Months -12 Days
10/10/2013 22 Aug 2000 13 Years 1 Months -12 Days
10/10/2013 22 Aug 2000 13 Years 1 Months -12 Days
10/10/2013 22 Aug 2000 13 Years 1 Months -12 Days
10/10/2013 22 Aug 2000 13 Years 1 Months -12 Days
10/10/2013 04 Sep 2012 1 Years 0 Months 6 Days
sudhakarssd
Updated on June 09, 2022Comments
-
sudhakarssd almost 2 years
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
Duration(years.months) 2.3
-
Adhikar Patil over 10 yearsThis query will helpful to find difference between two dates in Years, Months and days
-
Deenathaiyalan Shanmugam over 7 yearsCalculate Duration between from and to datetime datatype in Sql server, it will throw only hours, minutes, and seconds.
-
Paritosh over 7 yearsPlease edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this".