locked
count months query RRS feed

  • 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