Many a time we come across a scenario where we need to calculate the difference between two dates in Years, Months and days in Sql Server. In this article we will see how we can achieve this. This article covers the following:
- Difference between two dates in Years
- Difference between two dates in Months
- Difference between two dates in Days
- Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)
1. Difference between two dates in Years
Approach 1:
We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years
DECLARE @FromDate DATETIME = '2014-12-31', @ToDate DATETIME = '2015-01-01' SELECT @FromDate 'From Date', @ToDate 'To Date', DATEDIFF(YEAR, @FromDate, @ToDate) - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END) 'Date difference in Years'
Approach 2:
You may be thinking why such a complex logic is used to calculate the difference between two dates in years in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example
DECLARE @FromDate DATETIME = '2014-12-31', @ToDate DATETIME = '2015-01-01' SELECT @FromDate 'From Date', @ToDate 'To Date', DATEDIFF(YEAR, @FromDate, @ToDate) 'Date difference in Years'
From the above result it is clear that even-though @ToDate is the next day from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach. If we need to calculate age, then we should be using the Approach1.
2. Difference between two dates in Months
We can use DATEDIFF() function like below to get the difference between two dates in Months in Sql Server. DATEDIFF() functions first parameter value can be month or mm or m all will return the same result. Below example shows how we can get difference between two dates in Months
DECLARE @FromDate DATETIME = '2014-12-31', @ToDate DATETIME = '2015-01-01' SELECT @FromDate 'From Date', @ToDate 'To Date', (DATEDIFF(MONTH, @FromDate, @ToDate) - (CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END)) 'Date difference in Months'
Approach 2:
You may be thinking why such a complex logic is used to calculate the difference between two dates in months in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example
DECLARE @FromDate DATETIME = '2014-12-31', @ToDate DATETIME = '2015-01-01' SELECT @FromDate 'From Date', @ToDate 'To Date', DATEDIFF(MONTH, @FromDate, @ToDate) 'Date difference in Months'
From the above result it is clear that even-though the @ToDate is the next day from the @FromDate, but still the difference returned is 1 Month. So based on the need use the appropriate approach.
3. Difference between two dates in Days
We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years
DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', @ToDate DATETIME = '2015-07-11 00:00:00.000' SELECT @FromDate 'From Date', @ToDate 'To Date', (DATEDIFF(DAY, @FromDate, @ToDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @FromDate, @ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END)) 'Date difference in Days'
Approach 2:
You may be thinking why such a complex logic is used to calculate the difference between two dates in days in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example
DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', @ToDate DATETIME = '2015-07-11 00:00:00.000' SELECT @FromDate 'From Date', @ToDate 'To Date', DATEDIFF(DAY, @FromDate, @ToDate) 'Date difference in Days'
From the above result it is clear that even-though the @ToDate is the next second from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach.
4. Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)
We can use a script like below to get the difference between two dates in Years, Months and days.
DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', @ToDate DATETIME = '2015-01-02 00:00:00.000', @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate) - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate) SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate) SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) SELECT @FromDate FromDate, @ToDate ToDate, @Years Years, @Months Months, @Days Days
We can wrap the above script into a scalar function like below and can be reused for calculating the difference between dates
CREATE FUNCTION dbo.GetDateDifference ( @FromDate DATETIME, @ToDate DATETIME ) RETURNS NVARCHAR(100) AS BEGIN DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate) - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate) SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate) SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) + ' Months: ' + CAST(@Months AS VARCHAR(2)) + ' Days: ' + CAST(@Days AS VARCHAR(2)) END GO
Below example shows how we can use the above function in the select statement
DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', @ToDate DATETIME = '2015-01-02 00:00:00.000' SELECT @FromDate FromDate, @ToDate ToDate, dbo.GetDateDifference(@FromDate, @ToDate) AS 'Difference between dates'
We can as-well create a multi-statement table valued function like below to get difference between two dates in years, months and days
CREATE FUNCTION dbo.GetDateDifferenceInYearsMonthsDays ( @FromDate DATETIME, @ToDate DATETIME ) RETURNS @DateDifference TABLE ( YEAR INT, MONTH INT, DAYS INT) AS BEGIN DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate) - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate) SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate) SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate) - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate), @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) INSERT INTO @DateDifference VALUES(@Years, @Months, @Days) RETURN END
Below example shows how we can use the above function in the select statement
DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', @ToDate DATETIME = '2015-01-02 00:00:00.000' SELECT * FROM dbo.GetDateDifferenceInYearsMonthsDays(@FromDate,@ToDate) GO
This was very helpful, thanks.
It’s really helpful. Very good article
Thanks.. It was very helpfull
Thank you Mukesh… Appreciate your comments…
Using this as a reference: http://www.timeanddate.com/date/durationresult.html?d1=22&m1=05&y1=2002&d2=21&m2=06&y2=2002&ti=on
When I try to use the query from 22-May-2002 to 21-Jun-2002 (Include end date in calculation), the query is giving me 31 days as a result instead of 1 month. How can get around this? Here’s my query:
DECLARE @FromDate DATETIME = ‘2002-05-22 00:00:00.000’,
@ToDate DATETIME = ‘2002-06-21 00:00:00.000’,
@Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
– (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
@FromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days = 1+DATEDIFF(DAY, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SELECT @FromDate FromDate, @ToDate ToDate,
@Years Years, @Months Months, @Days Days
Hello,
Thanks for your code.
My friends Bienvenido, Oscar and me made some modification to the function to show only the data that has values.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Basavaraj Biradar
— Create date: July 10, 2015
— Description: Get difference between two dates in Years, Months and days in Sql Server
— =============================================
— Modified by: Bienvenido Lopez, Oscar Lopez and Martin Gonzalez, from Dominican Republic
— Modified date: April 29, 2016
create FUNCTION dbo.GetDateDifference
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Years INT
DECLARE @Months INT
DECLARE @Days INT
DECLARE @Horas INT
DECLARE @Minutos INT
DECLARE @Segundos INT
DECLARE @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
– (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
@FromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(DAY, @Days , @tmpFromDate)
SET @Horas = DATEDIFF(HOUR, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(HOUR, DATEDIFF(HOUR, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(HOUR, @Horas , @tmpFromDate)
SET @Minutos = DATEDIFF(MINUTE, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(MINUTE, DATEDIFF(MINUTE, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(MINUTE, @Minutos, @tmpFromDate)
SET @Segundos = DATEDIFF(SECOND, @tmpFromDate, @ToDate)
– (CASE WHEN DATEADD(SECOND, DATEDIFF(SECOND, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
RETURN CASE WHEN @Years>0 THEN CAST(@Years AS VARCHAR(4)) + ‘ Años ‘ ELSE ” END +
CASE WHEN @Months>0 THEN CAST(@Months AS VARCHAR(2)) + ‘ Meses ‘ ELSE ” END +
CASE WHEN @Days>0 THEN CAST(@Days AS VARCHAR(2)) + ‘ Dias ‘ ELSE ” END +
CASE WHEN @Horas>0 THEN CAST(@Horas AS VARCHAR(2)) + ‘ Horas ‘ ELSE ” END +
CASE WHEN @Minutos>0 THEN CAST(@Minutos AS VARCHAR(2)) + ‘ Minutos ‘ ELSE ” END +
CASE WHEN @Segundos>0 THEN CAST(@Segundos AS VARCHAR(2)) + ‘ Segundos ‘ ELSE ” END
END
Thanks Martin Gonzalez and Team for modifying and sharing it here. Hope it will help someone. Keep sharing and helping the Sql Community…
Using this as a reference: http://www.timeanddate.com/date/durationresult.html?d1=22&m1=05&y1=2002&d2=21&m2=06&y2=2002&ti=on
When I use the function for date specified below, it’s giving me 30 days instead of 1 month. How can i update the function?
SET @FromDate = ‘2002-05-22′
SET @ToDate=’2002-06-21’
As May has 31 days, if you will change @ToDate=’2002-06-22′ then it will change to 1 month
Lets look at this case for better understanding
@FromDate DATETIME = ‘2010-04-22 00:00:00.000’,
@ToDate DATETIME = ‘2010-05-21 00:00:00.000’
April has 30 days it gives 1 month for 2010-05-22 and 29 days for 2010-05-21.
Buhat aacha article hai.
Hi, In your first approach ‘year difference in years’
select dateadd (year, datediff (year,’2014-12-31′,’2015-01-01′),’2014-12-31′);
this results in 2015-12-31 which will be never greater than ‘2015-01-01’
hence showing year_diffrence as ‘1’ only.
select ‘2014-12-31′ as from_date,’2015-01-01′ as to_date,
(case dateadd(year,datediff(year,’2014-12-31′,’2015-01-01′),’2014-12-31’)
> ‘2015-01-01’ Then 1
else 0 END) as ‘year_diffrence;
This is giving me year_diffrence as ‘1’ only.
I owe you an apology, have forgot to minus the year difference. Please delete my post
Hi All,
MS-SQL Server:
How to calculate Number of Months completed between two Dates without using User Defined Functions:
Case 1:
Start_Date: ‘2016-01-16’
End_Date: ‘2016-01-16’
Expected output: 60
Case 2:
Start_Date: ‘2017-02-01’
End_Date: ‘2023-01-31’
Expected output: 72
Case 3:
Start_Date: ‘2018-12-01’
End_Date: ‘2022-04-30’
Expected output: 41