[Solved] difference between dates concatenated together

Question

Picture paints a thousand words

No! Don’t post pictures of data or code!

Your question is not at all clear, even with the picture. And – looking at my attempt to answer – you see, that I had to type in a test scenario. This was your job actually… For the next question please provide a working stand-alone scenario and add the expected output. Add the code you tried yourself and some explanation. This is worth a thousand words, pictures are annoying…

As told already: The question is not very clear, so this might be wrong for you, but my magic crystal ball tells me, that you are looking for this:

DECLARE @tbl TABLE(D1 DATE, D2 DATE);

INSERT INTO @tbl VALUES
 ({d'2016-02-01'},{d'2016-03-31'})
,({d'2015-10-01'},{d'2016-01-31'})  --Your Row 2
,({d'2016-09-01'},{d'2016-10-31'})  --Your Row 4
,({d'2015-12-01'},{d'2015-12-31'})
,({d'2018-04-01'},{d'2018-06-30'})
,({d'2018-04-04'},{d'2018-06-30'})  --Day index != 1, but smaller in D1
,({d'2018-04-04'},{d'2018-06-03'}); --Day index is smaller in D2

SELECT t.*
      ,A.CountOfMonths
      ,(
         SELECT TOP(A.CountOfMonths) CONCAT(' '
                                           ,FORMAT(DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,D1),'MMyy')
                                           ) 
         FROM master..spt_values
         FOR XML PATH('')
       )
FROM @tbl t
CROSS APPLY (SELECT DATEDIFF(MONTH,D1,D2)
                    + CASE WHEN DAY(D1)<DAY(D2) THEN 1 ELSE 0 END) A(CountOfMonths);

The result

D1          D2         CoM   
2016-02-01  2016-03-31  2    0216 0316
2015-10-01  2016-01-31  4    1015 1115 1215 0116
2016-09-01  2016-10-31  2    0916 1016
2015-12-01  2015-12-31  1    1215
2018-04-01  2018-06-30  3    0418 0518 0618
2018-04-04  2018-06-30  3    0418 0518 0618
2018-04-04  2018-06-03  2    0418 0518

The query will use some calculation to find the “NumberOfMonths” between the two dates. This value will be used to get the TOP() rows of a table. I use master..spt_values which is just a table with many rows. We are not interested in these values, just in a result set with the correct count of rows. On this set I apply ROW_NUMBER to get a running number (1,2 or 1,2,3,4 and so on). This running number is added in months to D1. The final output is done by FORMAT().

The row with the 4th of April shows, that your first date is not always the first of the month. So I added a case where the day’s index of the second month is smaller and I use a CASE to add one more month in “normal” situations. Might be, that you want to change this to <=… I don’t know what you want to happen if the day’s index is the same in D1 and D2…

If this does not solve your problem please use my scenario, add explanatory data and use the edit option of your question to make a good question out of it…

Accepted Answer