count months query

Question

User66371569 posted

Hi  I want query to count months different from column and system date

for example  if  column =4-11-2019    and system date= 5-12-2019       result =1 

if column = 4-11-2019    and system date= 4-12-2019     result= 0

if column = 4-10-2019    and system date= 5-12-2019   result=2

if column = 4-10-2019    and system date=4-12-2019   result=1

if column = 4-9-2019    and system date= 5-12-2019   result=3

if column = 4-9-2019    and system date=4-12-2019   result=2

I found some query    but   I don't know is it right or no.

can anyone modified it if there is any mistake.

select case when xx is null then 0  else xx end     -- remove null an replace 0

  from 
 

(
	select	datediff(month,changelogin_date,getdate()) +
 CASE
  WHEN DATEPART(day, getdate()) > DATEPART(day, changelogin_date)  THEN 1 ELSE 0 END
  xx

 from mm)a

Answer 1

User452040443 posted

Hi,

Try:

select	
    datediff(month, changelogin_date, getdate()) -
    CASE WHEN DATEPART(day, getdate()) > DATEPART(day, changelogin_date) THEN 0 ELSE 1 END

Hope this help

Answer 2

User77042963 posted

create table test (startDate date, endDate date)
insert into test values
('2019-11-04','2019-12-05')
,('2019-11-04','2019-12-04')
,('2019-10-04','2019-12-05')
,('2019-10-04','2019-12-04')
,('2019-09-04','2019-12-05')
,('2019-09-04','2019-12-04')


Select startDate,endDate,
Case when Datediff(month,startDate,endDate)<>0 
then
DATEDIFF(MONTH
, DATEADD(DAY,-DAY(startDate)+1,startDate)
,DATEADD(DAY,-DAY(startDate),endDate))
Else
0
End delta
from test


drop table test

Answer 3

User-1716253493 posted

case when datecol>getdate() then DATEDIFF(month,getdate(),datecol) else DATEDIFF(month,datecol,getdate()) end

Answer 4

User77042963 posted

case when datecol>getdate() then DATEDIFF(month,getdate(),datecol) else DATEDIFF(month,datecol,getdate()) end

What this for?

Answer 5

User-1151440187 posted

According to your question I have written the below query : 

select startDate, endDate, ((DATEDIFF(month,startDate,endDate)-1) + case when DATEPART(day,endDate)>DATEPART(day,startDate) then 1 else 0 end) as Difference from TableName;

I hope this will help you. Let me know if you are doing this query.