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.
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.months_between(date1, date2)/12
As usual, Ask Tom has a nice answer: an implementation of DateDiff in PL/SQL. Looks nice and tidy too.
Re:Depends on your dialect
grinder on 2007-04-02T08:58:43
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.
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 anyway. It served its purpose.years:= trunc((to_number(to_char(date2, 'YYYYMMDD'))-to_number(to_char(date1, 'YYYYMMDD')))/10000)
Re:Try this...
grinder on 2007-04-02T08:53:24
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.