-3

answer should be correct for all possible scenarios. below i have write some of possible scenario.

Eg -

  1. Number of month between 2012-12-31 and 2013-02-28 Answer should be - 2

  2. Number of month between 2019-01-31 and 2019-03-01 Answer should be - 1

Shehan Silva
  • 526
  • 4
  • 8

1 Answers1

-2

A simple and easy way, just copy and paste this example SQL to MSSQL and execute. If you want, you can create a function using this code and execute.

DECLARE @StartDate DATE = '2019-01-31'
DECLARE @EndDate DATE = '2019-02-28'

SELECT
   DATEDIFF(MONTH, @StartDate, @EndDate)+
   (CASE
    WHEN FORMAT(@StartDate,'yyyy-MM') != FORMAT(@EndDate,'yyyy-MM') AND 
      DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) AND 
      DATEPART(DAY,@EndDate) = DATEPART(DAY,EOMONTH(@EndDate)) THEN 0

    WHEN FORMAT(@StartDate,'yyyy-MM') != FORMAT(@EndDate,'yyyy-MM') AND 
      DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) THEN -1 

    ELSE 0 END) AS NumberOfMonths
Stuart Frankish
  • 818
  • 1
  • 11
  • 27
Shehan Silva
  • 526
  • 4
  • 8
  • The sample data in the question, and the start date you use here are not the same. Why do you need a `CASE` expression, when `DATEDIFF(MONTH,' 20121231','20130228')` already returns `2`? – Thom A Jan 06 '20 at 11:21
  • You posted [the same answer](https://stackoverflow.com/a/54855814/1364007) on the question that this has been closed as a duplicate of. Instead of copying answers, flag to close the question as a duplicate. – Wai Ha Lee Jan 06 '20 at 11:28
  • @Larnu because without case expression we get wrong answer in some scenarios. Eg- please check. 2019-01-31 and 2019-03-01. – Shehan Silva Jan 06 '20 at 11:29
  • You gave **one** example in your "question" @ShehanSilva , 2012-12-31 and 2013-02-28, and said the answer should be 2. `DATEDIFF(MONTH,' 20121231','20130228')` returns `2`. You give no further details in your "question" about the required logic, or what the goal is; so `DATEDIFF(MONTH...` does the job for what you specified. – Thom A Jan 06 '20 at 11:37
  • @Larnu. but when we give a solution for a question we should investigate what's the actual problem they are facing. in order to that we should consider all the possible scenarios and answer should be correct for all the possible scenarios. otherwise no point of posting a answer and users may mislead. i have posted this answer bcz i have deeply tested this code for all possible scenarios. thanks – Shehan Silva Jan 07 '20 at 07:12
  • *"but when we give a solution for a question we should investigate what's the actual problem they are facing"* And how do we do that when the OP (you) doesn't tell us that and then answers their own vague question with an answer that isn't representative of the question they have asked? If a question is vague, they don't get answered with vague/guessed, they get closed; that is the policy on SO. – Thom A Jan 07 '20 at 11:39