All the Perl that's Practical to Extract and Report
use Perl Log In
grinder (1100)
(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.
Calculating years (anniversaries) between dates in SQL
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.
help them think they understand.
--Larry Wall
Depends on your dialect (Score:2)
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:
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.
Re: (Score:1)
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.
Try this... (Score:2)
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
Re: (Score:1)
Thanks!
TIMESTAMPs and INTERVALs (Score:2)
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