Asked by:
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
Wednesday, December 4, 2019 6:56 AM
All replies
-
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
Wednesday, December 4, 2019 12:26 PM -
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
Wednesday, December 4, 2019 3:03 PM -
User-1716253493 posted
case when datecol>getdate() then DATEDIFF(month,getdate(),datecol) else DATEDIFF(month,datecol,getdate()) end
Thursday, December 5, 2019 12:40 AM -
User77042963 posted
case when datecol>getdate() then DATEDIFF(month,getdate(),datecol) else DATEDIFF(month,datecol,getdate()) end
What this for?
Thursday, December 5, 2019 2:30 PM -
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.
Tuesday, February 4, 2020 5:46 AM