Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

grinder (1100)

grinder
  (email not shown publicly)
http://www.landgren.net/perl/
Yahoo! ID: perlgrinder (Add User, Send Message)

Editor of p5p summaries, member of the p5p peanut gallery.

Journal of grinder (1100)

Friday March 30, 2007
04:29 AM

Calculating years (anniversaries) between dates in SQL

[ #32843 ]

Dear Lazyweb,

I've just spent the better part of an hour searching the web and coming up blank, and finally coding up my own SQL statement to solve the following problem: given a two dates, how many years apart are they? I didn't want to use Oracle's date minus date equals number of days, because I wanted to avoid the hassle of dealing with leap years.

select
    case
    when (to_char(sysdate, 'MM'  ) - to_char(to_date(:dt), 'MM')) > 0
        then (to_char(sysdate, 'YYYY') - to_char(to_date(:dt), 'YYYY'))
    when (to_char(sysdate, 'MM'  ) - to_char(to_date(:dt), 'MM')) < 0
        then (to_char(sysdate, 'YYYY') - to_char(to_date(:dt), 'YYYY')) - 1
    else
        case when (to_char(sysdate, 'DD'  ) - to_char(to_date(:dt), 'DD')) >= 0
        then
            (to_char(sysdate, 'YYYY') - to_char(to_date(:dt), 'YYYY'))
        else
            (to_char(sysdate, 'YYYY') - to_char(to_date(:dt), 'YYYY')) - 1
        end
    end as "y"
    -- ,(to_char(sysdate, 'MM'  ) - to_char(to_date(:dt), 'MM'))   as "m",
    -- ,(to_char(sysdate, 'DD'  ) - to_char(to_date(:dt), 'DD'))   as "d"
from dual

I've tested a number of boundary conditions and it seems correct to me.

Now, after you stop sniggering, you may show me the function I overlooked that would have done this for me, or point out any obvious bugs.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Date functions aren't really standard in SQL, so it's a question of what RDBMS you're using. For Sybase and SQL server, you could use datediff(yy, @date1, @date2).

    Looks like you've got Oracle. In which case I think if you used months, you wouldn't have to worry about leap years:

    months_between(date1, date2)/12

    But I haven't tested that.

    As usual, Ask Tom has a nice answer: an implementation of DateDiff in PL/SQL [oracle.com]. Looks nice and tidy too.

    • The trouble with months_between is that it doesn't return nice sharp integers, it returns floats. This always makes me wonder about things like whether 11.6 months round up to a year.

      One more damned thing to go wrong, and all that.

  • I don't have access to an Oracle system right now, but this might just work.

    years := trunc((to_number(to_char(date2, 'YYYYMMDD'))-to_number(to_char(date1, 'YYYYMMDD')))/10000)

    The idea is that you just want to subtract the years, but when the value for 'MMDD' for the lower date (date1), taken as a 4 digit number, is larger than that of the later (date2), then you'll have a "borrow" (AKA "carry" for subtraction) from the years. And then, you'll just throw the days/months value away, as it's incorrect anywa

    • ooh sweet, I like this. The yyyymmdd insight is very clever. According to some quick tests it checks out just fine.

      Thanks!
  • With most recent versions of Oracle, you can use the ANSI-SQL standard TIMESTAMP date types and associated functions, which will give you INTERVAL data types when you subtract, and those can be fairly intelligently converted to years. I haven't gotten to use these as much as I like, but so far they have been very nice.

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers