Search code examples
sqlsql-serverdatediffdate-difference

SQL Server 2012 : DATEDIFF function in terms of years, days and months

I am using the DATEDIFF function in SQL Server 2012. I have two dates, 2015-01-01 and current_date. I need DATEDIFF in terms of Years , Months & Days between these two dates. below are my statements :

SELECT DATEDIFF(YY, '2015-01-01', GETDATE()) AS 'Years'
SELECT DATEDIFF(MM, '2015-01-01', GETDATE()) AS 'Months'
SELECT DATEDIFF(DD, '2015-01-01', GETDATE()) AS 'Days'

The result I am getting is: Years = 3, Months = 46, Days = 1416

Why in months & days it is adding an extra year?

Solution

  • DATEDIFF() measures the number of time boundaries between two date/time values.

    So, with year, it measures the number of times that the year flips (i.e. that new years start).

    With month, it measures the number of times that the month flips (i.e. that new months start).

    With day, it measures the number of times that the day flips (i.e. that new days start).

    These are all independent of each other.

    I would recommend that you not try to get the value in years/months/days. This is a hard problem. What is the number of months/days between Feb 28 and Mar 31? Between Jan 31 and Feb 28? Between Jan 31 and Mar 31? They don't add up, making this a hard problem.