Volume 8, Number 37 -- October 29, 2008

Calculate a Fractional Number of Years Difference Between Two Dates in SQL

Published: 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(
       ,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') 

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

Sponsored By

Don't Break the Bank,
We Can Keep You Rolling!

Select any of our productivity tools and ask to finance thru our Lease-to-Own Plan.

Lease-to-Own RDB Connect or DBU, database and connectivity utilities
over a three year period for only a few dollars a day.
No risks, no hassles, no problems.

Download your FREE 30 day trial today!


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

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

The Four Hundred
IBM's Q3 in Servers, Redux: The i and p Platforms Do OK

So Much For That Unbundled i Compiler Tool Pricing

SOA Without the Middleware, Without the Pressure

As I See It: The IT Election

Gartner Outlines the Key IT for 2009

The Linux Beacon
Why Blade Servers Still Don't Cut It, and How They Might

Intel Keeps Both Arms Swinging with Xeons, Jabs with Itanium

Microsoft Ponies Up Another $100 Million for Novell Linux

Mad Dog 21/21: Newtonian Economics

Two More Xeon-Based Galaxy Servers from Sun

Four Hundred Stuff
Vision Integrates Clustering Tool into IBM's New Web Console

Databorough Teams with Genuitec to Push Alternative Eclipse IDE

Computer Retailer CDW Taps Linoma for Database Encryption

Lawson Delivers Demand Planning, BI Tools to M3 Customers

Shop-Vac Ditches IBM, Taps CYBRA for Native i OS RFID Software

Big Iron
For Some Customers, the Mainframe Is Green

Top Mainframe Stories From Around the Web

Chats, Webinars, Seminars, Shows, and Other Happenings

System i PTF Guide
October 25, 2008: Volume 10, Number 43

October 18, 2008: Volume 10, Number 42

October 11, 2008: Volume 10, Number 41

October 4, 2008: Volume 10, Number 40

September 27, 2008: Volume 10, Number 39

September 20, 2008: Volume 10, Number 38

The Windows Observer
Citrix Addresses Performance with XenApp 5

Server Buyers Shop Like It's 1999 in the Second Quarter

Intel Keeps Both Arms Swinging with Xeons, Jabs with Itanium

Mad Dog 21/21: Newtonian Economics

Microsoft Does Something About Those SQL Injection Attacks

The Unix Guardian
What the Heck Is the Midrange, Anyway?

Overseas and Notebook Sales Offset Printer Declines for HP in Q3

Two More Xeon-Based Galaxy Servers from Sun

Mad Dog 21/21: Newtonian Economics

Intel's Nehalems to Star at IDF, AMD Pitches Shanghai

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar


ProData Computer Services
WorksRight Software

Printer Friendly Version

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

Four Hundred Guru


From the IT Jungle Forums
Data Queues vs. MQ Series: Performance

Removing blanks from a CL Variable


SQL "Hidden" Field

Java Messages

MQ Help Desired

Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement