CODEWITHSUNDEEP
sqlsql-server
Jeswanth
Jeswanth

Reputation: 197

Month difference between two dates in sql server

Please refer the below examples and kindly let me know your ideas.

declare @EmployeeStartDate datetime='01-Sep-2013'
declare @EmployeeEndDate datetime='15-Nov-2013'
select DateDiff(mm,@EmployeeStartDate, DateAdd(mm, 1,@EmployeeEndDate))

Output = 3 expected output = 2.5

Since I have only 15 days in Nov, So I should get 0.5 for Nov

Upvotes: 5

Views: 62513

Answers (7)

chandra shekar reddy dubba
chandra shekar reddy dubba

Reputation: 85

You can use Below to calculate the No Of Months between two Dates in MySQL, 

PERIOD_DIFF(concat(year(Date1),LPAD(month(Date1),2,0)),concat(year(Date2),LPAD(month(Date2),2,0)))

Upvotes: -2

user1308314
user1308314

Reputation: 31

declare @EmployeeStartDate datetime='013-09-01'
declare @EmployeeEndDate datetime='2013-11-15'
SELECT DATEDIFF(month, @EmployeeStartDate, @EmployeeEndDate)

Thanks for this. https://www.w3schools.com/sql/func_sqlserver_datediff.asp

Upvotes: 0

pius
pius

Reputation: 2424

As far as I can tell, none of the other answers take into account that not all months are exactly 30 days long.

This is what I came up with:

DECLARE @StartDate DATETIME = '07-Oct-2018'
DECLARE @EndDate DATETIME = '06-Nov-2018'
SELECT
    DATEDIFF(m, @StartDate, @EndDate)
    + 1.0 * DAY(@EndDate) / DAY(EOMONTH(@EndDate))
    - 1.0 * DAY(@StartDate) / DAY(EOMONTH(@StartDate))

-- 0.974193548388

The formula can be explained like this:

  1. The difference in months as output by DATEDIFF(m, ..., ...)
  2. Plus the day of the last date divided by the number of days in that month
  3. Minus the day of the first date divided by the number of days in that month

Note that in this case the answer from Ren Yuzhi gives the result 1.006451612904. The 1.0 is necessary to make the division happen in floating point rather than integer.

Upvotes: 1

Ren Yuzhi
Ren Yuzhi

Reputation: 29

select CAST(DATEDIFF(MONTH, StartDate, EndDate) AS float) -
  (DATEPART(dd,StartDate)*1.0 - 1.0) / DAY(EOMONTH(StartDate)) +
  ((DATEPART(dd,EndDate)*1.0 ) / DAY(EOMONTH(EndDate)))

Upvotes: 2

Obsidian Phoenix
Obsidian Phoenix

Reputation: 4155

DateDiff compares the values of the column you specify to work out the difference, it doesn't compare both dates and give you an exact difference. You've told it to compare the Month values, so thats all it's looking it.

http://technet.microsoft.com/en-us/library/ms189794.aspx

The Technet article details the return value of the DateDiff Function - note that it's only int.

If you want the value as an exact figure (or nearabouts), you should datediff the dates on days, then divide by 30. For neatness, I've also rounded to a single decimal place.

select Round(Convert(decimal, DateDiff(dd,@EmployeeStartDate, @EmployeeEndDate)) / 30, 1)

Upvotes: 2

Vishal Suthar
Vishal Suthar

Reputation: 17194

Here you go:

declare @EmployeeStartDate datetime='01-Sep-2013'
declare @EmployeeEndDate datetime='15-Nov-2013'

;WITH cDayDiff AS
(
   select DateDiff(dd,@EmployeeStartDate, DateAdd(dd, 1,@EmployeeEndDate)) as days
)
SELECT
   CAST(days as float) / 30  as Months
FROM
   cDayDiff

It has 76 days which equals to 2.5333

Output:

Months
============
2.53333333333333

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try this

SELECT CASE WHEN DATEDIFF(d,'2013-09-01', '2013-11-15')>30 THEN DATEDIFF(d,'2013-09-01', '2013-11-15')/30.0 ELSE 0 END AS 'MonthDifference'

OR

SELECT DATEDIFF(DAY, '2013-09-01', '2013-11-15') / 30.436875E

Upvotes: 10

Related Questions