I need to calculate the number of FULL month in SQL, i.e.
I tried to use DATEDIFF, i.e.
SELECT DATEDIFF(MONTH, '2009-04-16', '2009-05-15')
but instead of giving me full months between the two date, it gives me the difference of the month part, i.e.
1
anyone know how to calculate the number of full months in SQL Server?
The original post had some bugs... so I re-wrote and packaged it as a UDF.
CREATE FUNCTION FullMonthsSeparation
(
@DateA DATETIME,
@DateB DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT
DECLARE @DateX DATETIME
DECLARE @DateY DATETIME
IF(@DateA < @DateB)
BEGIN
SET @DateX = @DateA
SET @DateY = @DateB
END
ELSE
BEGIN
SET @DateX = @DateB
SET @DateY = @DateA
END
SET @Result = (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)
RETURN @Result
END
GO
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-15') as MonthSep -- =0
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-16') as MonthSep -- =1
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-06-16') as MonthSep -- =2
What's your definition of a month? Technically a month can be 28,29,30 or 31 days depending on the month and leap years.
It seems you're considering a month to be 30 days since in your example you disregarded that May has 31 days, so why not just do the following?
SELECT DATEDIFF(DAY, '2009-04-16', '2009-05-15')/30
, DATEDIFF(DAY, '2009-04-16', '2009-05-16')/30
, DATEDIFF(DAY, '2009-04-16', '2009-06-16')/30
I know this is an old question, but as long as the dates are >= 01-Jan-1753 I use:
DATEDIFF(MONTH, DATEADD(DAY,-DAY(@Start)+1,@Start),DATEADD(DAY,-DAY(@Start)+1,@End))
DATEDIFF() is designed to return the number boundaries crossed between the two dates for the span specified. To get it to do what you want, you need to make an additional adjustment to account for when the dates cross a boundary but don't complete the full span.
Is not necesary to create the function only the @result part. For example:
Select Name,
(SELECT CASE WHEN
DATEPART(DAY, '2016-08-28') > DATEPART(DAY, '2016-09-29')
THEN DATEDIFF(MONTH, '2016-08-28', '2016-09-29') - 1
ELSE DATEDIFF(MONTH, '2016-08-28', '2016-09-29') END) as NumberOfMonths
FROM
tableExample;
I realize this is an old post, but I created this interesting solution that I think is easy to implement using a CASE statement.
Estimate the difference using DATEDIFF, and then test the months before and after using DATEADD to find the best date. This assumes Jan 31 to Feb 28 is 1 month (because it is).
DECLARE @First date = '2015-08-31'
DECLARE @Last date = '2016-02-28'
SELECT
@First as [First],
@Last as [Last],
DateDiff(Month, @First, @Last) as [DateDiff Thinks],
CASE
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) +1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) +1
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) , @First) <= @Last Then DATEDIFF(Month, @First, @Last)
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) -1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) -1
END as [Actual Months Apart]
SIMPLE AND EASY WAY, Just Copy and Paste this FULL code to MS SQL and Execute :
declare @StartDate date='2019-01-31'
declare @EndDate date='2019-02-28'
SELECT
DATEDIFF(MONTH, @StartDate, @EndDate)+
(
case
when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) AND DATEPART(DAY,@EndDate) = DATEPART(DAY,EOMONTH(@EndDate)) then 0
when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) then -1
else 0
end
)
as NumberOfMonths
All you need to do is deduct the additional month if the end date has not yet passed the day of the month in the start date.
DECLARE @StartDate AS DATE = '2019-07-17'
DECLARE @EndDate AS DATE = '2019-09-15'
DECLARE @MonthDiff AS INT = DATEDIFF(MONTH,@StartDate,@EndDate)
SELECT @MonthDiff -
CASE
WHEN FORMAT(@StartDate,'dd') > FORMAT(@EndDate,'dd') THEN 1
ELSE 0
END
You can create this function to calculate absolute difference between two dates. As I found using DATEDIFF inbuilt system function we will get the difference only in months, days and years. For example : Let say there are two dates 18-Jan-2018 and 15-Jan-2019. So the difference between those dates will be given by DATEDIFF in month as 12 months where as it is actually 11 Months 28 Days. So using the function given below, we can find absolute difference between two dates.
CREATE FUNCTION GetDurationInMonthAndDays(@First_Date DateTime,@Second_Date DateTime)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @RESULT VARCHAR(500)=''
DECLARE @MONTHS TABLE(MONTH_ID INT,MONTH_NAME VARCHAR(100),MONTH_DAYS INT)
INSERT INTO @MONTHS
SELECT 1,'Jan',31
union SELECT 2,'Feb',28
union SELECT 3,'Mar',31
union SELECT 4,'Apr',30
union SELECT 5,'May',31
union SELECT 6,'Jun',30
union SELECT 7,'Jul',31
union SELECT 8,'Aug',31
union SELECT 9,'Sep',30
union SELECT 10,'Oct',31
union SELECT 11,'Nov',30
union SELECT 12,'Jan',31
IF(@Second_Date>@First_Date)
BEGIN
declare @month int=0
declare @days int=0
declare @first_year int
declare @second_year int
SELECT @first_year=Year(@First_Date)
SELECT @second_year=Year(@Second_Date)+1
declare @first_month int
declare @second_month int
SELECT @first_month=Month(@First_Date)
SELECT @second_month=Month(@Second_Date)
if(@first_month=2)
begin
IF((@first_year%100<>0) AND (@first_year%4=0) OR (@first_year%400=0))
BEGIN
SELECT @days=29-day(@First_Date)
END
else
begin
SELECT @days=28-day(@First_Date)
end
end
else
begin
SELECT @days=(SELECT MONTH_DAYS FROM @MONTHS WHERE MONTH_ID=@first_month)-day(@First_Date)
end
SELECT @first_month=@first_month+1
WHILE @first_year<@second_year
BEGIN
if(@first_month=13)
begin
set @first_month=1
end
WHILE @first_month<13
BEGIN
if(@first_year=Year(@Second_Date))
begin
if(@first_month=@second_month)
begin
SELECT @days=@days+DAY(@Second_Date)
break;
end
else
begin
SELECT @month=@month+1
end
end
ELSE
BEGIN
SELECT @month=@month+1
END
SET @first_month=@first_month+1
END
SET @first_year = @first_year + 1
END
select @month=@month+(@days/30)
select @days=@days%30
if(@days>0)
begin
SELECT @RESULT=CAST(@month AS VARCHAR)+' Month '+CAST(@days AS VARCHAR)+' Days '
end
else
begin
SELECT @RESULT=CAST(@month AS VARCHAR)+' Month '
end
END
ELSE
BEGIN
SELECT @RESULT='ERROR'
END
RETURN @RESULT
END
SELECT dateadd(dd,number,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS gun FROM master..spt_values
WHERE type = 'p'
AND year(dateadd(dd,number,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))=year(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
CREATE FUNCTION ufFullMonthDif (@dStart DATE, @dEnd DATE)
RETURNS INT
AS
BEGIN
DECLARE @dif INT,
@dEnd2 DATE
SET @dif = DATEDIFF(MONTH, @dStart, @dEnd)
SET @dEnd2 = DATEADD (MONTH, @dif, @dStart)
IF @dEnd2 > @dEnd
SET @dif = @dif - 1
RETURN @dif
END
GO
SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-01')
SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-29')
SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-30')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-05-15')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-05-16')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-06-16')
SELECT dbo.ufFullMonthDif ('2019-01-31', '2019-02-28')
Making Some changes to the Above function worked for me.
CREATE FUNCTION [dbo].[FullMonthsSeparation] ( @DateA DATETIME, @DateB DATETIME ) RETURNS INT AS BEGIN DECLARE @Result INT
DECLARE @DateX DATETIME
DECLARE @DateY DATETIME
IF(@DateA < @DateB)
BEGIN
SET @DateX = @DateA
SET @DateY = @DateB
END
ELSE
BEGIN
SET @DateX = @DateB
SET @DateY = @DateA
END
SET @Result = (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
THEN DATEDIFF(MONTH, @DateX, @DateY) - iif(EOMONTH(@DateY) = @DateY, 0, 1)
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)
RETURN @Result
END
Declare @FromDate datetime, @ToDate datetime,
@TotalMonth int ='2021-10-01', @TotalDay='2021-12-31' int,
@Month int = 0
WHILE @ToDate > DATEADD(MONTH,@Month,@FromDate)
BEGIN
SET @Month = @Month +1
END
SET @TotalMonth = @Month -1
SET @TotalDay = DATEDIFF(DAY, DATEADD(MONTH,@TotalMonth, @FromDate),@ToDate) +1
IF(@TotalDay = DAY(EOMONTH(@ToDate)))
BEGIN
SET @TotalMonth = @TotalMonth +1
SET @TotalDay =0
END
Result @TotalMonth = 3, @TotalDay=0
if you are using PostGres only --
SELECT (DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date)) * 12 +
(DATE_PART('month', '2012-01-01'::date) - DATE_PART('month', '2011-10-02'::date));
There are a lot of answers here that did not satisfy all the corner cases so I set about to fix them. This handles:
I think this generally handles all the cases needed.
declare @dateX date = '01/1/2022'
declare @datey date = '02/28/2022'
-- select datediff(month, @dateX, @datey) --Here for comparison
SELECT
CASE
WHEN DATEPART(DAY, @DateX) = 1 and DATEPART(DAY, @DateY) = DATEPART(DAY, eomonth(@DateY))
THEN DATEDIFF(MONTH, @DateX, @DateY) + 1
WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY) and DATEPART(DAY, @DateY) != DATEPART(DAY, eomonth(@DateY))
THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
I got some ideas from the other answers, but none of them gave me exactly what I wanted.
The problem boils down to what I perceive a "month between" to be, which may be what others are also looking for also.
For example 25th February to 25th March would be one month to me, even though it is only 28 days. I would also consider 25th March to 25th April as one month at 31 days.
Also, I would consider 31st January to 2nd March as 1 month and 2 days even though it is 30 days between.
Also, fractions of a month are a bit meaningless as it depends on the length of a month and which month in the range do you choose to take a fraction of.
So, with that in mind, I came up with this function. It returns a decimal, the integer part is the number of months and the decimal part is the number of days, so a return value of 3.07 would mean 3 months and 7 days.
CREATE FUNCTION MonthsAndDaysBetween (@fromDt date, @toDt date)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @d1 date, @d2 date, @numM int, @numD int, @trc varchar(10);
IF(@fromDt < @toDt)
BEGIN
SET @d1 = @fromDt;
SET @d2 = @toDt;
END
ELSE
BEGIN
SET @d1 = @toDt;
SET @d2 = @fromDt;
END
IF DAY(@d1)>DAY(@d2)
SET @numM = year(@d2)*12+month(@d2)-year(@d1)*12-month(@d1)-1;
ELSE
SET @numM = year(@d2)*12+month(@d2)-year(@d1)*12-month(@d1);
IF YEAR(@d1) < YEAR(@d2) OR (YEAR(@d1) = YEAR(@d2) AND MONTH(@d1) < MONTH(@d2))
BEGIN
IF DAY(@d2) < DAY(@d1)
SET @numD = DAY(@d2) + DAY(EOMONTH(DATEADD(month,-1,@d2))) - DAY(@d1);
ELSE
SET @numD = DAY(@d2)-DAY(@d1);
END
ELSE
SET @numD = DAY(@d2)-DAY(@d1);
RETURN @numM + ABS(@numD) / 100.0;
END
I believe it is important to note that the question specifically asks for "full months between" AND that in the examples given each date is treated as "the START point of that date". This latter item is important because some comments state that year-01-31 to year-02-28 is a result of zero. This is correct. 1 complete day in January, plus 27 complete days in February (02-28 is the start of that day, so incomplete) is zero "full" months.
With that in mind I believe the following would meet the requirements IF StartDate is <= EndDate
(DATEPART(YEAR, EndDate) - DATEPART(YEAR, StartDate)) * 12
+ (DATEPART(MONTH, EndDate) - DATEPART(MONTH, StartDate))
- CASE WHEN DATEPART(DAY,EndDate) < DATEPART(DAY,StartDate) THEN 1 ELSE 0 END
To accommodate the possibility that the dates may be in any order then:
, CASE WHEN StartDate <= EndDate THEN
(DATEPART(YEAR, EndDate) - DATEPART(YEAR, StartDate)) * 12
+ (DATEPART(MONTH, EndDate) - DATEPART(MONTH, StartDate))
- CASE WHEN DATEPART(DAY,EndDate) < DATEPART(DAY,StartDate) THEN 1 ELSE 0 END
ELSE
(DATEPART(YEAR, StartDate) - DATEPART(YEAR, EndDate)) * 12
+ (DATEPART(MONTH, StartDate) - DATEPART(MONTH, EndDate))
- CASE WHEN DATEPART(DAY,StartDate) < DATEPART(DAY,EndDate) THEN 1 ELSE 0 END
END AS FullMnthsBtwn
For this sample:
select
StartDate, EndDate
into mytable
from (
values
(cast(getdate() as date),cast(getdate() as date)) -- both same date
-- original
,('2009-04-16','2009-05-15') -- > 0 full month
,('2009-04-16','2009-05-16') -- > 1 full month
,('2009-04-16','2009-06-16') -- > 2 full months
-- '1/31/2018' and endDate of '3/1/2018', I get a 0 – Eugene
, ('2018-01-31','2018-03-01')
-- some extras mentioned in comments, both of these should return 0 (in my opinion)
,('2009-01-31','2009-02-28')
,('2012-12-31','2013-02-28')
,('2022-05-15','2022-04-16') -- > 0 full month
,('2022-05-16','2022-04-16') -- > 1 full month
,('2021-06-16','2022-04-16') -- > 10 full months
) d (StartDate, EndDate)
query
select
StartDate
, EndDate
, CASE WHEN StartDate <= EndDate THEN
(DATEPART(YEAR, EndDate) - DATEPART(YEAR, StartDate)) * 12
+ (DATEPART(MONTH, EndDate) - DATEPART(MONTH, StartDate))
- CASE WHEN DATEPART(DAY,EndDate) < DATEPART(DAY,StartDate) THEN 1 ELSE 0 END
ELSE
(DATEPART(YEAR, StartDate) - DATEPART(YEAR, EndDate)) * 12
+ (DATEPART(MONTH, StartDate) - DATEPART(MONTH, EndDate))
- CASE WHEN DATEPART(DAY,StartDate) < DATEPART(DAY,EndDate) THEN 1 ELSE 0 END
END AS FullMnthsBtwn
from mytable
order by 1
result
+------------+------------+---------------+
| StartDate | EndDate | FullMnthsBtwn |
+------------+------------+---------------+
| 2009-01-31 | 2009-02-28 | 0 |
| 2009-04-16 | 2009-05-15 | 0 |
| 2009-04-16 | 2009-05-16 | 1 |
| 2009-04-16 | 2009-06-16 | 2 |
| 2012-12-31 | 2013-02-28 | 1 |
| 2018-01-31 | 2018-03-01 | 1 |
| 2021-06-16 | 2022-04-16 | 10 |
| 2022-05-15 | 2022-04-16 | 0 |
| 2022-05-16 | 2022-04-16 | 1 |
| 2022-07-09 | 2022-07-09 | 0 |
+------------+------------+---------------+
See db<>fiddle here (compares some other responses as well)
SELECT 12 * (YEAR(end_date) - YEAR(start_date)) +
((MONTH(end_date) - MONTH(start_date))) +
SIGN(DAY(end_date) / DAY(start_date));
This works fine for me on SQL SERVER 2000.
UPDATED Right now, I just use
SELECT DATEDIFF(MONTH, '2019-01-31', '2019-02-28')
and SQL server returns the exact result (1).
I googled over internet. And suggestion I found is to add +1 to the end.
Try do it like this:
Declare @Start DateTime
Declare @End DateTime
Set @Start = '11/1/07'
Set @End = '2/29/08'
Select DateDiff(Month, @Start, @End + 1)