EXACT MONTH Difference in SQL SERVER

Question

I want to find the exact month difference. for e.g. 6 Months difference from today, Consider Today and 6 Months before is 2018-01-14. If you see the dates 6 Months is over and it is 6 Months ,1 day.I want to display it as 7 months . i tried the below query but it returns complete months.

select DATEDIFF(MONTH,'2018-01-15 10:49:36.237',GETDATE())

score 0 · Answer 1 · answered Jul 16 '18 at 05:40

0

Two ways:

One using select:

select 
datediff(month,'2012-April-09','2013-April-08') MonthsInService
,datediff(day,'2012-April-09','2013-April-08')/365 YearsInService

Second using a function:

CREATE FUNCTION [dbo].[getFullYears] 
(
    @dateX datetime,
    @dateY datetime
)  
RETURNS int
AS  
BEGIN 
    DECLARE @y int
    SET @y =DATEDIFF(year,@dateX,@dateY)
    IF (@dateY < DATEADD(year, @y, @dateX)) SET @y = @y -1
    RETURN @y
END

select dbo.getFullYears('2012-April-09','2013-April-09') --1
select dbo.getFullYears('2012-April-09','2013-April-08') --0

refer this post as well for month calculation, it will complete my answer overall.

answered Jul 16 '18 at 05:40

Barr J

10,636
1
28
46

Select Statement returns complete months but i want incomplete months. for e.g. datediff will return 6 month 10 days as 6 months and 6 months 29 days also as 6 months. – Srini Jul 16 '18 at 05:43
this is why I gave you in days as well, look at the above datediff: ` ,datediff(day,'2012-April-09','2013-April-08')/365 YearsInService` – Barr J Jul 16 '18 at 05:45

score 0 · Answer 2 · answered Jul 16 '18 at 05:43

You can find year, month and day by this

 WITH ex_table AS (
      SELECT '2018-01-15 10:49:36.237' 'monthbefore',
             getdate() 'visitdatetime')
    SELECT CAST(DATEDIFF(yy, t.monthbefore, t.visitdatetime) AS varchar(4)) +' year '+
           CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.monthbefore, t.visitdatetime), t.monthbefore), t.visitdatetime) AS varchar(2)) +' month '+
           CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.monthbefore, t.visitdatetime), t.monthbefore), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.monthbefore, t.visitdatetime), t.monthbefore)), t.visitdatetime) AS varchar(2)) +' day' AS result
      FROM ex_table t

Md. Suman Kabir · Answer 3 · 2018-07-16T05:55:51.777

0

You can use this :

declare @date as varchar(50)
set @date = '2018-01-15 10:49:36.237'

select DATEDIFF(MONTH, @date, GETDATE()) as [Month], DATEDIFF(day, @date, GETDATE())%30 as [Day]

Output

Month   Day
6       2

edited Jul 16 '18 at 05:55

answered Jul 16 '18 at 05:49

Md. Suman Kabir

5,243
5
25
43

EXACT MONTH Difference in SQL SERVER

3 Answers3