0

calculate the month difference between two dates

Its working fine with dateDiff(m, datestart , dateend ) but i have the scenario of calculate the month based on day.like if 01/01/2012 to 20/01/2012 must show as the result of 1 month.

and another scenario is 02/01/2012 to 03/02/102 must show the as the result of 2 months.

how can i do that.

jai
  • 582
  • 1
  • 6
  • 20
  • Use as `dateDiff(d, datestart , dateend )/30` – Amit Apr 03 '14 at 11:49
  • @AmitAgrawal: I think it will still bring up 0 – huMpty duMpty Apr 03 '14 at 11:53
  • @huMptyduMpty if month is less then 1 – Amit Apr 03 '14 at 11:53
  • 1
    @AmitAgrawal: What happen in this case `Select dateDiff(d, '1/30/2012' , '2/1/2012' )/30` OP expect to see 2 as result!! – huMpty duMpty Apr 03 '14 at 11:56
  • @huMptyduMpty Difference is not 2 months – Amit Apr 03 '14 at 11:56
  • in every options i just got 0 as the answer – jai Apr 03 '14 at 11:57
  • this is the scenario SELECT DATEDIFF(MONTH,'2014-02-01 00:00:00.000','2014-04-03 00:00:00.000') AS total in that i must get 3 as a output because the end date is much bigger than start date(that means it has 2 extra days) – jai Apr 03 '14 at 11:58
  • Do you have any examples where just adding 1 to the result of `DATEDIFF(month,...` isn't the right answer? – Damien_The_Unbeliever Apr 03 '14 at 12:11
  • k for example start date='2014-02-01 00:00:00.000', and End date is='2014-04-03 00:00:00.000' so while on datediff function returns 2 month as a output.But in original scenario it takes "2 month and 1 day".Like in my thoughts is day is still remains it must add +1 month to the output month.got it? – jai Apr 03 '14 at 12:17
  • It will work: http://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql – Thirisangu Ramanathan Jul 24 '14 at 10:32

4 Answers4

0

How about this,

SELECT CASE
WHEN
DATEPART(MONTH,'STARTDATE') = DATEPART (MONTH,'ENDDATE')
THEN
DATEPART(MONTH,'STARTDATE')
ELSE
DATEDIFF(MONTH,'STARTDATE','ENDDATE') 
END
AS 'MONTH-DIFFERENCE' 
FROM TABLE_NAME
Aditya
  • 2,299
  • 5
  • 32
  • 54
0

Try This,

Declare @sDate date = '1/15/2014'
Declare @eDate date = '3/10/2014'

Select DATEDIFF(mm,@sDate,@eDate) - Case When datepart(DD,@sDate) > DATEPART(dd,@eDate) Then  1 else 0 end

SQL Fiddle DEMO

AK47
  • 3,707
  • 3
  • 17
  • 36
0

I resolved it. SELECT
BadgeNo AS [Badge No], JB.GHRSBadgeNo AS [GHRS No],
(ISNULL(Emp.FirstName,'') + ' ' + ISNULL(Emp.MiddleName,'') + ' '+ ISNULL(Emp.LastName,'')) AS [Name],
JB.Department AS [Department],
JB.ServiceCompany AS [Service Company],
REPLACE(CONVERT(VARCHAR(11), Emp.BirthDate, 106), ' ', '-') AS [Birth Date]
FROM
Employee AS Emp
LEFT JOIN
jobdata AS JB
ON
EMP.BadgeNo = JB.BadgeNo
WHERE
1 = (FLOOR(DATEDIFF(dd,EMP.BirthDate,DATEADD(month, 1,GETDATE())) / 365.25)) - (FLOOR(DATEDIFF(dd,EMP.BirthDate,GETDATE()) / 365.25))

jai
  • 582
  • 1
  • 6
  • 20
-1

Use the same function and Add 1 to the result e.g.

(dateDiff(m, datestart , dateend )+1) as Month
Undo
  • 25,519
  • 37
  • 106
  • 129
Meer
  • 656
  • 9
  • 18