CODEWITHSUNDEEP
sql-server-2008-r2roundingdatediff
NonProgrammer
NonProgrammer

Reputation: 1387

Get difference between two dates in Years

I am working with a table that has StartDate and EndDate fields. I need to find difference between then in years.

Example: StartDate = 1/1/2017 EndDate = 12/31/2017

I expect Result = 1 for the date difference.

Also, I'd like to round it to nearest whole number.

Example: StartDate = 1/1/2017 EndDate = 11/30/2017

I expect Result = 1 for the date difference.

Using datediff function, I am able to get the result, but it isn't rounding to nearest whole number.

Example query: I am getting 6 years even though 65 months / 12 would be less than 5.5:

select (DATEDIFF(yy, '01/01/2016', '5/31/2021')
+ CASE WHEN abs(DATEPART(day, '01/01/2016') - DATEPART(day, '05/31/2021')) > 15 THEN 1 ELSE 0 END) 
select (DATEDIFF(mm, '01/01/2016', '05/31/2021')
+ CASE WHEN abs(DATEPART(day, '01/01/2016') - DATEPART(day, '05/31/2021')) > 15 THEN 1 ELSE 0 END)

Upvotes: 0

Views: 1852

Answers (3)

LewisJor
LewisJor

Reputation: 1

This may be a bit old but when using Oracle SQL Developer you can use the following. Just add your Dates below. I was using DateTime. This was used to get years between 0 and 10.

TRUNC((MONTHS_BETWEEN(<DATE_ONE>, <DATE_TWO>) * 31) / 365) > 0 and TRUNC((MONTHS_BETWEEN(<DATE_ONE>, <DATE_TWO>) * 31) / 365) < 10

Upvotes: 0

user8327466
user8327466

Reputation: 

DECLARE @startdate DATETIME = '1-1-2017', 
        @enddate DATETIME = '12-31-2018'
SELECT @startdate as StartDate, @enddate as EndDate,
 DATEDIFF(YEAR, @startdate, @enddate)
   -
 (CASE
   WHEN DATEADD(YEAR, 
           DATEDIFF(YEAR, @startdate,@enddate), @startdate)
       > @enddate THEN 1 
         ELSE 0 END) 'Date difference in Years'

Use this code, I hope it will help you.

Upvotes: 1

NonProgrammer
NonProgrammer

Reputation: 1387

So far following query seems to be working okay. My mistake was I dividing by 12 instead of 12.0 for rounding to work correctly. Who knew! :

select 
Round((DATEDIFF(mm, '01/01/2016', '07/1/2017')
    + CASE WHEN abs(DATEPART(day,  '01/01/2016') - DATEPART(day, '06/30/2017')) > 15 THEN 1 ELSE 0 END) / 12.0, 0)

Upvotes: 0

Related Questions