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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Calculating years (anniversaries) between dates in SQL 5 Comments More | Login | Reply /

 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
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.