locked
Get number of months from number of days RRS feed

  • Question

  • Hello

    I have 2 dates (in the form MM/DD/yyyy) representing the start date of the employee and the end date of the fiscal year:

    the employee started working on 12/15/2010 and the fiscal year ends on 08/31/2011

    I want to calculate the number of months that the employee worked between those 2 dates

    in reality this employee will have been working until 08/31/2011 for 8.5 months

    datediff function returns 8

    how to calculate exact number of months in sql?

    thank you in advance

    Friday, October 1, 2010 8:33 AM

Answers

  • If a person will work for 7 days, then is it will become 8.2 months??????

    check this...it will returns No of months completed and days......

    declare @startdate datetime = '12/15/2010'
    declare @enddate datetime = '8/31/2011'
    
    ;with cte as
    (
    	select 0 as Months,@startdate as Sdate,DATEDIFF(dd,@StartDate,@enddate) as DiffDays
    	union all
    	select Months+1,DATEADD(MONTH,1,sdate),DATEDIFF(dd,DATEADD(MONTH,1,sdate),@enddate)
    	 from cte where Sdate <= DATEADD(month,-1,@enddate)
    )
    select top 1 Months,DiffDays,CAST( Months + round((DiffDays*1.0/30),1) as decimal(8,2))  from cte order by Months desc
    
    
    
    
    
    
    • Marked as answer by alihijazi Friday, October 1, 2010 9:13 AM
    Friday, October 1, 2010 8:49 AM

All replies

  • If a person will work for 7 days, then is it will become 8.2 months??????

    check this...it will returns No of months completed and days......

    declare @startdate datetime = '12/15/2010'
    declare @enddate datetime = '8/31/2011'
    
    ;with cte as
    (
    	select 0 as Months,@startdate as Sdate,DATEDIFF(dd,@StartDate,@enddate) as DiffDays
    	union all
    	select Months+1,DATEADD(MONTH,1,sdate),DATEDIFF(dd,DATEADD(MONTH,1,sdate),@enddate)
    	 from cte where Sdate <= DATEADD(month,-1,@enddate)
    )
    select top 1 Months,DiffDays,CAST( Months + round((DiffDays*1.0/30),1) as decimal(8,2))  from cte order by Months desc
    
    
    
    
    
    
    • Marked as answer by alihijazi Friday, October 1, 2010 9:13 AM
    Friday, October 1, 2010 8:49 AM
  • employee will be working for 5 days per week
    Friday, October 1, 2010 9:04 AM
  • Depending on how accurate you need the calculation it may be worth setting up a calendar table instead.

    
    SELECT CAST(DATEDIFF(DAY,'20101215','20110831')/30.437500 AS NUMERIC(4,1))
    

    Jon
    • Proposed as answer by Muhammad Abbas Friday, October 1, 2010 9:11 AM
    Friday, October 1, 2010 9:07 AM
  • Check the above query and let us know it suits u or not?

    Friday, October 1, 2010 9:07 AM
  • ok roughly speaking the above query suites my requirements!!
    Friday, October 1, 2010 9:12 AM