• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Calculate a Fractional Number of Years Difference Between Two Dates in SQL

    October 29, 2008 Hey, Mike

    Is there anything new in SQL that will calculate the difference between two dates and return a fractional number of years? For example: “2007-09-10”–“1997-01-01” should return 10.69 years.

    The normal DB2 SQL function to return a difference between two timestamps (TIMESTAMP_DIFF) will not return fractional values as your calculation requires.

    However, if super accuracy isn’t a great need, there is an easy way to do this. Simply calculate the number of days between the two dates to get the number of days difference. Then divide the result by 365.2425, which is the average number of days in the Gregorian calendar, and you have a fractional number of years difference. For more info on using 365.2425 as the number of days in a year, click here. Other websites have the average number of days as 365.2422 although the value is gradually declining.

    Here’s an SQL example using host variables that rounds the result to two decimals:

    Select Round(
           (Days(:EndDate)-Days(:StartDate))/365.2425
           ,2) As No_Years
      Into :NoYears
      From SysIBM/SysDummy1
    

    And there you have it, an easy way to calculate a fractional number of years between two dates in SQL.

    On a related note, this same technique can be used to get the fractional number between two months; but this is a little sloppy when using an average of 30.4369 days per month. However, starting in V6R1 the new MONTHS_BETWEEN function can be used to better estimate the fractional number of months between two dates without the SQL mess shown above. Here is an example from the IBM manual:

    SELECT MONTHS_BETWEEN('2005-02-20', '2005-01-17') 
      FROM SYSIBM.SYSDUMMY1
    

    This example will return the value 1.096774193548387.

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Michael to Ted Holt via the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Quadrant Software:  Going paperless pays off! Watch our exclusive Webinar series
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada
    Vision Solutions:  A $20 gas card for completing a short i5/OS DR survey

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Global’s Executive Dash Gets Closer Hooks to Excel Is the Smart Cube the New i?

    Leave a Reply Cancel reply

Volume 8, Number 37 -- October 29, 2008
THIS ISSUE SPONSORED BY:

ProData Computer Services
Aldon
WorksRight Software

Table of Contents

  • Calculate a Fractional Number of Years Difference Between Two Dates in SQL
  • Meaningful Names for Null Indicators
  • Pimp My Font Size, Win a No-Prize

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle