No of Months between two dates in YYYYMM format

  • I am looking to calculate no of months between two dates which are in YYYYMM format.

    Like no of months between 201505 and 201305

  • sqlinterset (6/15/2015)


    I am looking to calculate no of months between two dates which are in YYYYMM format.

    Like no of months between 201505 and 201305

    WITH DATEFIELDS AS (

    SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'

    )

    SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHS

    FROM DATEFIELDS;

    This assumes you have a character based DATEFIELD1 and DATEFIELD2.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • oops, misread the question.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • sgmunson (6/15/2015)


    sqlinterset (6/15/2015)


    I am looking to calculate no of months between two dates which are in YYYYMM format.

    Like no of months between 201505 and 201305

    WITH DATEFIELDS AS (

    SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'

    )

    SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHS

    FROM DATEFIELDS;

    This assumes you have a character based DATEFIELD1 and DATEFIELD2.

    You don't need to do it that lengthy. YYYYMMDD is an ISO standard and SQL Server will identify it regardless of any configuration (YYYY-MM-DD can cause problems).

    WITH DATEFIELDS AS (

    SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505'

    )

    SELECT DATEDIFF(month, DATEFIELD1 + '01', DATEFIELD2 + '01') AS DIFF_MONTHS

    FROM DATEFIELDS;

    By the way, whenever possible, use date/time data types for this. For full months, you can use day 1. Using date data types allows you to use datetime functions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sqlinterset (6/15/2015)


    I am looking to calculate no of months between two dates which are in YYYYMM format.

    Like no of months between 201505 and 201305

    I know the question has been answered but I have to ask... Since months can be 28, 29, 30, or 31 days and the two dates can represent partial months, it would be really interesting to find out what you mean by a "month". Are you talking simply about the boundary dates of months or are you looking for a particular duration that would represent a month?

    For example, what do you want to return for 20160228 and 20160331?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • To get months and days...

    declare @a1 date

    declare @a2 date

    select @a1 = '2014-01-05',

    @a2 = '2014-02-04'

    --Select CASE WHEN DAY(@a1) < Day(@a2)

    SelectCASE WHEN DAY(@a1) <= Day(@a2) THEN

    DATEDIFF(MONTH , @A1,@A2)

    ELSE

    DATEDIFF(MONTH , @A1,@A2) - 1

    END as Months,

    CASE WHEN DAY(@a1) <= Day(@a2) THEN

    DATEDIFF(DAY,

    DateAdd(month, DATEDIFF(MONTH , @A1,@A2), @a1),

    @a2

    )

    else

    DATEDIFF(DAY,

    DateAdd(month, DATEDIFF(MONTH , @A1,@A2) - 1, @a1),

    @a2

    )

    End as Days

  • I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/16/2015)


    I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    I guess the definition of a month for this case is very simple as the OP is not dealing with days, just a year-month combination.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Jeff Moden - Thursday, July 16, 2015 8:56 AM

    I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    This is the way I solved that issue in my report:

    =iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

  • robert.steele 53480 - Thursday, December 7, 2017 10:09 AM

    Jeff Moden - Thursday, July 16, 2015 8:56 AM

    I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    This is the way I solved that issue in my report:

    =iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

    I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, December 7, 2017 3:25 PM

    robert.steele 53480 - Thursday, December 7, 2017 10:09 AM

    Jeff Moden - Thursday, July 16, 2015 8:56 AM

    I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    This is the way I solved that issue in my report:

    =iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

    I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?

    No it wouldn't work without first using a Convert Command.  If you don't have the day value stored you won't be able to get the true accurate date between the two.  Sorry I thought you had the day.

  • robert.steele 53480 - Friday, December 8, 2017 4:11 AM

    Jeff Moden - Thursday, December 7, 2017 3:25 PM

    robert.steele 53480 - Thursday, December 7, 2017 10:09 AM

    Jeff Moden - Thursday, July 16, 2015 8:56 AM

    I still want to know what someone considers to be a month. DATEDIFF(mm,'20150630','20150701') will say that a month has passed when only 1 day will have passed. What is the definition of a "month" for this case? 30 Days? 30.2 days? 4 Weeks? 5-4-4? What?

    This is the way I solved that issue in my report:

    =iif(Day(Fields!"FieldName".Value)<=Day(Today()),DateDiff("m",Fields!"FieldName".Value,Today()),DateDiff("m",Fields!"FieldName".Value,Today())-1)

    I'm no genius when it comes to SSRS, SSIS, etc so I have to ask... will that work when the data I a character based value in the format of YYYYMM with no day?

    No it wouldn't work without first using a Convert Command.  If you don't have the day value stored you won't be able to get the true accurate date between the two.  Sorry I thought you had the day.

    Thank you, good Sir, for the feedback.  I appreciate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares - Monday, June 15, 2015 1:50 PM

    sgmunson (6/15/2015)


    sqlinterset (6/15/2015)


    I am looking to calculate no of months between two dates which are in YYYYMM format.Like no of months between 201505 and 201305

    WITH DATEFIELDS AS (SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505')SELECT DATEDIFF(month, LEFT(DATEFIELD1, 4) + '-' + RIGHT(DATEFIELD1, 2) + '-01', LEFT(DATEFIELD2, 4) + '-' + RIGHT(DATEFIELD2, 2) + '-01') AS DIFF_MONTHSFROM DATEFIELDS;This assumes you have a character based DATEFIELD1 and DATEFIELD2.

    You don't need to do it that lengthy. YYYYMMDD is an ISO standard and SQL Server will identify it regardless of any configuration (YYYY-MM-DD can cause problems).WITH DATEFIELDS AS (SELECT DATEFIELD1 = '201503', DATEFIELD2 = '201505')SELECT DATEDIFF(month, DATEFIELD1 + '01', DATEFIELD2 + '01') AS DIFF_MONTHSFROM DATEFIELDS;By the way, whenever possible, use date/time data types for this. For full months, you can use day 1. Using date data types allows you to use datetime functions.

    Not sure why other posts on this thread have more likes but, IMHO, Luis' post is the way to go whether you're playing the code against a table or doing a singleton check.  It would work very well as a high performance iTVF.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If the values are integers, I don't see the need to involve date conversions/char stringing at all:


    SELECT yyyymm1, yyyymm2, (yyyymm2 / 100 * 12 + yyyymm2 % 100) - (yyyymm1 / 100 * 12 + yyyymm1 % 100)
    FROM (VALUES(201305,201505),(201505,201505),(201701,201702)) AS testdata(yyyymm1,yyyymm2)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply