CODEWITHSUNDEEP
sql-server
Swazzy
Swazzy

Reputation: 97

How to Calculate the number of months between two dates in YYYYMM Format?

For example I want to calculate the month difference from 190001

 Product  | YrMo   | Month_Num | 
 A        | 201602 | 1392      |
 A        | 201603 | 1393      |
 B        | 201605 | 1396      |

Can Datediff be manipulated or is there another method?

Upvotes: 0

Views: 2621

Answers (2)

Lewis Worley
Lewis Worley

Reputation: 279

--If they are integers, this is most likely the fastest (fewest conversions)

--INTEGER division by 100 gets you the year.

--MOD division by 100 gets you the month.

DECLARE @STARTDATE INT = 190001;
DECLARE @ENDDATE INT = 201602;

select (@ENDDATE/100 - @STARTDATE/100) * 12 
           + (@ENDDATE % 100 - @STARTDATE % 100);

OR

SELECT (YrMo/ 100 - 190001 / 100) * 12 + (YrMo % 100 - 190001 % 100)

Upvotes: 2

Matt
Matt

Reputation: 14341

SELECT
    Product
    ,YrMo
    ,DATEDIFF(month,'1/1/1900',DATEFROMPARTS(LEFT(YrMo,4),RIGHT(YrMo,2),1)) AS Month_Num
FROm
    TableName

Seeing you know it is always 6 digits just split up the string and build a date from part year, month, and then 1 for day and use DATEDIFF from there.

Upvotes: 1

Related Questions