Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2012
    Posts
    32

    Unanswered: Date difference expressed in months

    Hello

    I'm using DB2 9.5 and need to obtain the difference between 2 DATE columns and express the result in months.
    I'm aware that there is a function called MONTHS BETWEEN but that is for DB2 version 10 or at least definitely not for version 9.5.

    Is there a workaround?

    Thanks & regards

    S. BASU

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use either

    a) TIMESTAMPDIFF function

    Refer documentation on the syntax and also the assumptions that are made when using the function


    b) date1 - date2

    Here you will be getting the interger output of difference in YYMMDD , ie
    if the difference is 10 years 1 month 2 days, then it will be
    100102
    If it is 1 month
    100 (integer value of 000100)

    so, to get difference in months, it will be

    ((date1 - date2)*12/10000)+((date1-date2)/100))
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2012
    Posts
    32
    Used option B but didn't return the same result as a calendar difference gave me.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by S. BASU View Post
    Used option B but didn't return the same result as a calendar difference gave me.
    what do you mean by calendar difference ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by sathyaram_s View Post
    what do you mean by calendar difference ?
    This might be ...
    ( YEAR(date1) - YEAR(date2) ) * 12 + MONTH(date1) - MONTH(date2)


    Anyway,
    S. BASU should clarify his(her?) difinition of "calendar difference".

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Here is another slight variation:

    (YEAR(DT_COL2 - DT_COL1) * 12 ) + (MONTH(DT_COL2 - DT_COL1))

    This (along with the other examples) only counts whole months. If you want to take days into account (more than 1/2 the days = 1 month, for example), you will need to define you requirement a little bit.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are some examples.

    Code:
    SELECT date1 , date2
         , DEC( MONTHS_BETWEEN(date1 , date2) , 11 , 5 )   AS months_between
         , INT( ROUND( MONTHS_BETWEEN(date1 , date2) ) )   AS round_mon_btwn
         , ( YEAR( date1) - YEAR( date2) ) * 12
           + MONTH(date1) - MONTH(date2)                   AS months_ton
         , YEAR(date1 - date2) * 12 + MONTH(date1 - date2) AS months_stealth
     FROM  (VALUES
               ( DATE('2013-02-28') , DATE('2013-01-28') )
             , ( DATE('2013-02-28') , DATE('2013-01-29') )
             , ( DATE('2013-02-28') , DATE('2013-01-30') )
             , ( DATE('2013-02-28') , DATE('2013-01-31') )
             , ( DATE('2013-05-01') , DATE('2013-03-07') )
             , ( DATE('2013-05-08') , DATE('2013-03-07') )
             , ( DATE('2014-01-01') , DATE('2013-01-16') )
             , ( DATE('2014-01-01') , DATE('2013-01-17') )
             , ( DATE('2014-03-01') , DATE('2014-02-16') )
             , ( DATE('2014-03-01') , DATE('2014-02-17') )
           ) AS t(date1 , date2);
    ------------------------------------------------------------------------------
    
    DATE1      DATE2      MONTHS_BETWEEN ROUND_MON_BTWN MONTHS_TON  MONTHS_STEALTH
    ---------- ---------- -------------- -------------- ----------- --------------
    2013-02-28 2013-01-28        1.00000              1           1              1
    2013-02-28 2013-01-29        0.96774              1           1              0
    2013-02-28 2013-01-30        0.93548              1           1              0
    2013-02-28 2013-01-31        1.00000              1           1              0
    2013-05-01 2013-03-07        1.80645              2           2              1
    2013-05-08 2013-03-07        2.03225              2           2              2
    2014-01-01 2013-01-16       11.51612             12          12             11
    2014-01-01 2013-01-17       11.48387             11          12             11
    2014-03-01 2014-02-16        0.51612              1           1              0
    2014-03-01 2014-02-17        0.48387              0           1              0
    
      10 record(s) selected.
    Tesed on
    Code:
    D:\IBM\SQLLIB_V97\tools>db2level
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with 
    level identifier "08060107".
    Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23286_27924", 
    and Fix Pack "5".
    Product is installed at "D:\IBM\SQLLIB_V97" with DB2 Copy Name "DB2COPY1".
    Last edited by tonkuma; 03-21-13 at 05:49. Reason: Add last two test data.

  8. #8
    Join Date
    Apr 2012
    Posts
    32
    By calendar difference I meant the difference I obtained from the site:

    Date Duration Calculator: Days between two dates

    I see that Stealth_DBA's solution ((YEAR(DT_COL2 - DT_COL1) * 12 ) + (MONTH(DT_COL2 - DT_COL1))) works fine which becomes:

    SELECT (YEAR(DATE('9999-12-31') - DATE('2008-01-01')) * 12 ) + (MONTH(DATE('9999-12-31') - DATE('2008-01-01'))) FROM sysibm.sysdummy1

    The correct answer tallied at 95903

    However yesterday I tried solution B by sathyaram_s which was:

    ((date1 - date2)*12/10000)+((date1-date2)/100)) which becomes:

    SELECT ((DATE('9999-12-31') - DATE('2008-01-01'))*12/10000) + ((DATE('9999-12-31') - DATE('2008-01-01'))/100) FROM sysibm.sysdummy1

    and this gave me: 895004,656 (Decimal 31,23)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by S. BASU View Post
    By calendar difference I meant the difference I obtained from the site:

    Date Duration Calculator: Days between two dates

    ...
    But, it calculates days between two dates, and NOT months between two dates.

    I added two test data including your new data and two expressions(columns) which were revised solution B by sathyaram_s

    Which expression(s) was best fit to your requirements?
    Note: First two expressions using MONTHS_BETWEEN wouldn't work on DB2 9.5, as you already wrote.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT date1 , date2
         , DEC( MONTHS_BETWEEN(date1 , date2) , 11 , 5 )   AS months_between
         , INT( ROUND( MONTHS_BETWEEN(date1 , date2) ) )   AS round_mon_btwn
         , ( YEAR( date1) - YEAR( date2) ) * 12
           + MONTH(date1) - MONTH(date2)                   AS months_ton
         , YEAR(date1 - date2) * 12 + MONTH(date1 - date2) AS months_stealth
         , (date1 - date2) * 12 / 10000 + MOD((date1-date2) / 100 , 100) AS sathyaram_s_b
         , INT(
              (date1 - date2) * 12 / 10000 + MOD((date1-date2) / 100 , 100)
           ) AS int_sathyaram
     FROM  (VALUES
               ( DATE('2013-02-28') , DATE('2013-01-28') )
             , ( DATE('2013-02-28') , DATE('2013-01-29') )
             , ( DATE('2013-02-28') , DATE('2013-01-30') )
             , ( DATE('2013-02-28') , DATE('2013-01-31') )
             , ( DATE('2013-05-01') , DATE('2013-03-07') )
             , ( DATE('2013-05-08') , DATE('2013-03-07') )
             , ( DATE('2014-01-01') , DATE('2013-01-16') )
             , ( DATE('2014-01-01') , DATE('2013-01-17') )
             , ( DATE('2014-03-01') , DATE('2014-02-16') )
             , ( DATE('2014-03-01') , DATE('2014-02-17') )
             , ( DATE('9999-12-31') , DATE('2008-01-01') )
             , ( DATE('9999-12-30') , DATE('2008-01-31') )
           ) AS t(date1 , date2)
    ;
    ------------------------------------------------------------------------------
    
    DATE1      DATE2      MONTHS_BETWEEN ROUND_MON_BTWN MONTHS_TON  MONTHS_STEALTH SATHYARAM_S_B                     INT_SATHYARAM
    ---------- ---------- -------------- -------------- ----------- -------------- --------------------------------- -------------
    2013-02-28 2013-01-28        1.00000              1           1              1                    1.120000000000             1
    2013-02-28 2013-01-29        0.96774              1           1              0                    0.036000000000             0
    2013-02-28 2013-01-30        0.93548              1           1              0                    0.034800000000             0
    2013-02-28 2013-01-31        1.00000              1           1              0                    0.033600000000             0
    2013-05-01 2013-03-07        1.80645              2           2              1                    1.150000000000             1
    2013-05-08 2013-03-07        2.03225              2           2              2                    2.241200000000             2
    2014-01-01 2013-01-16       11.51612             12          12             11                   12.339200000000            12
    2014-01-01 2013-01-17       11.48387             11          12             11                   12.338000000000            12
    2014-03-01 2014-02-16        0.51612              1           1              0                    0.015600000000             0
    2014-03-01 2014-02-17        0.48387              0           1              0                    0.014400000000             0
    9999-12-31 2008-01-01    95903.96774          95904       95903          95903                95904.356000000000         95904
    9999-12-30 2008-01-31    95902.96774          95903       95903          95902                95903.236000000000         95903
    
      12 record(s) selected.

  10. #10
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    But, it calculates days between two dates, and NOT months between two dates.
    I understand but since I needed just the total number of months I disregarded the days and converted the result to just months in order to compare with the formula I used.

    I have finally gone for Stealth's solution i.e. YEAR(date1 - date2) * 12 + MONTH(date1 - date2)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •