9 Replies Latest reply: Oct 19, 2012 11:25 AM by Brian Garside RSS

    Subtract two dates to get the number of months

      I have two SQL date fields. I need to find the number of months between the two and display in a single column.

      The issue I have its returnign strnage dates where the year is 97 or 00. Im sure I have to use Num#() etc.. to get a numeric value.

       

      StartDate - EndDate = # of Months

       

      I prefer to keep the solution as an Expression

        • Re: Subtract two dates to get the number of months

          SQL function on you load script:

           

          datediff(mm,StartDate,EndDate)

          • Re: Subtract two dates to get the number of months

            If you follow sweul's link, it provides an interesting solution...

             

            year(end)*12+month(end)-year(Start)*12+month(Start)

             

            The problem is 4/1/12 - 3/31/12 will result in 1 month. 

            just as 3/31/12 - 2/1/12 will result in one month.

             

            If that's what you need then use this.

              • Re: Subtract two dates to get the number of months

                This returned 43 months for dates 10/1/2012 - 9/30/2014 using the above Exp

                If I removed the * 12 it returns 21 which is close.

                 

                While the DateDiff = 23 which is correct. so SQL wins this one. Far superior to QV date functions

                • Re: Subtract two dates to get the number of months
                  John Witherspoon

                  klangley wrote:

                   

                  If you follow sweul's link, it provides an interesting solution...

                   

                  year(end)*12+month(end)-year(Start)*12+month(Start)

                   

                  The problem is 4/1/12 - 3/31/12 will result in 1 month. 

                  just as 3/31/12 - 2/1/12 will result in one month.

                   

                  If that's what you need then use this.

                   

                  Brian Garside wrote:

                   

                  This returned 43 months for dates 10/1/2012 - 9/30/2014 using the above Exp

                  If I removed the * 12 it returns 21 which is close.

                   

                  While the DateDiff = 23 which is correct. so SQL wins this one. Far superior to QV date functions

                   

                  I agree that QlikView's handling of dates is rather poor.  That said, QlikView can't be blamed for sign errors (+month(Start)) or eliminating a necessary term from the expression (*12).  The expression should be:

                   

                  year(End)*12+month(End)-year(Start)*12-month(Start)

                   

                  Though I'd write it:

                   

                  (year(End)-year(Start))*12+(month(End)-month(Start))

                   

                  And yes, I know I could eliminate a pair of parentheses.  Either expression should be the equivalent of datediff() for months - they count the month boundaries crossed.  You are correct that 4/1/12-3/31/12 = 4/30/12-3/1/12 = 1 month.  That's what datediff() does.  But I agree that it might not match someone's requirement.

                   

                  There are two other ways of counting months that I think would be common.  First, you might want the time period rounded to the number of months.  That's simple enough, though you'll probably never remember the magic number for the average number of days in a month.  Fortunately, there's always wikipedia.

                   

                  round((End-Start)/30.436875)

                   

                  And second, you might want the number of months in the range.  So for 4/1/12-3/31/12 you'd want two months, both March and April.  That's simply the original expression +1, unless your start can come after your end, in which case you'd have to subtract 1 for that, assuming you wanted to see negative months in that case.  So perhaps:

                   

                  year(End)*12+month(End)-year(Start)*12-month(Start)+if(Start<=End,1,-1)

                   

                  All expressions untested, so I may have made silly errors myself. 

                   

                  As I said, I agree that QlikView's handling of dates is poor.  We shouldn't have to do any of this.  Earlier this year I wrote a suite of date difference functions in COBOL - days, weeks, months, quarters or years like datediff(), or datediff()+/-1, or rounded.  The code where everything happens is less than 100 lines.  If I can write a full suite of date difference functions in less than 100 lines of COBOL, I can't understand why QlikTech can't put something together.

                   

                  Maybe there's already something written for the QlikView Components project, or maybe a full suite of date difference functions could be added.  I haven't looked at it in a long time, so I'm not sure what's available these days.

                   

                  http://code.google.com/p/qlikview-components/