CODEWITHSUNDEEP
sqlt-sqldatesql-server-2008-r2
sudhakarssd
sudhakarssd

Reputation: 451

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 

Duration(years.months)
2.3

Upvotes: 3

Views: 20415

Answers (8)

Deenathaiyalan Shanmugam
Deenathaiyalan Shanmugam

Reputation: 60

declare @StartDate datetime, @EndDate datetime
select @StartDate = '2016-08-24 14:44:54.513',@EndDate='2016-08-24 14:48:16.173'
Select convert(varchar,CAST((@EndDate-@StartDate) as time(0))) '[hh:mm:ss]'

Upvotes: 0

Charles Haynie
Charles Haynie

Reputation: 1

This is my function for finding the difference in time between two dates.

ALTER FUNCTION functionName
-- The first imput is the start time and the second input is the end time.
@StartTime datetime, @EndTime datetime

RETURNS varchar(15)
AS
BEGIN

    DECLARE @Duration varchar(15)


SET @Duration =
convert(nvarchar(10),(COALESCE (DATEDIFF(day,@StartTime,@EndTime),DATEDIFF(day,@StartTime,CURRENT_TIMESTAMP)))) + '.' +
right('00'+ISNULL(convert(nvarchar(10),(COALESCE (DATEDIFF(hour,@StartTime,@EndTime)%24,DATEDIFF(hour,@StartTime,CURRENT_TIMESTAMP)%24))),' '),2) + ':' +
right('00'+ISNULL(convert(nvarchar(10),(COALESCE (DATEDIFF(minute,@StartTime,@EndTime)%60,DATEDIFF(minute,@StartTime,CURRENT_TIMESTAMP)%60))),' '),2) + ':' +
right('00'+ISNULL(convert(nvarchar(10),(COALESCE (DATEDIFF(second,@StartTime,@EndTime)%60,DATEDIFF(second,@StartTime,CURRENT_TIMESTAMP)%60))),' '),2)
    -- Calculates the difference in each until of time, finds the remainder, and add the pieces of the timestamp together.
    RETURN @Duration

END

Upvotes: 0

Adhikar Patil
Adhikar Patil

Reputation: 11

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

Upvotes: 1

nnnn
nnnn

Reputation: 1051

I tried by this-

    SELECT
      CONVERT(varchar,DATEDIFF(MONTH,SAKUSEITIME,KOSHINTIME)/12)+'.'
      +CONVERT(varchar,DATEDIFF(MONTH,SAKUSEITIME,KOSHINTIME)%12) Duration
    FROM MYTABLE

Upvotes: 0

Murtuza Kabul
Murtuza Kabul

Reputation: 6514

Try this one. It might help

select cast (DATEDIFF(m,'06-Oct-2009','15-Jul-2011')/12 as varchar(25)) + '.' + cast (DATEDIFF(m,'06-Oct-2009','15-Jul-2011')%12 as varchar(25))

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

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.

Upvotes: 9

Buzz
Buzz

Reputation: 6320

try something like this

SELECT
    DATEDIFF( mm, start_dt, end_dt) / 12 AS years
    , DATEDIFF( mm, start_dt, end_dt) % 12 AS months

Upvotes: 2

CloudyMarble
CloudyMarble

Reputation: 37566

SELECT (DATEDIFF(month, start_dt, end_dt) / 12);

Upvotes: 0

Related Questions