Re: How Many Years have Passed?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> I have checked the FAQ and other sources and learned that the difference
> between two dates is the integer number of days. I want to know how many
> years are between those two dates.

> Is the only way to do that to do something like checking the julian day
> first to see if the second one is higher (i.e. it's past my birthday),
> then doing date_part('year', foo) - date_part('year', bar) and adjusting
> for whether my birthday has passed?

"Julian day" is the number of days since 1 January 4713 BC. This number
will only help you determine which date is chronologically first.

The "Julian date", the number of days since January 1st in a given
year, is not of much help either, since leap years can cause the same
dates to have different Julian dates for different years.

The best way I have come up with is the following, which accepts any two
dates ('AAA' and 'BBB') and spits out the years between them. If you want
March 1, 2000 to March 1, 2001 to NOT count as a full year, just
change the < and > at the end of the extract('day') lines to <= and >=

SELECT CASE WHEN
(
( extract('year' FROM datetime('AAA')) -
extract('year' FROM datetime('BBB')) > 0
)
AND
(
( extract('month' FROM datetime('AAA')) <
extract('month' FROM datetime('BBB'))
)
OR
(
( extract('month' FROM datetime('AAA')) =
extract('month' FROM datetime('BBB'))
)
AND
( extract('day' FROM datetime('AAA')) <
extract('day' FROM datetime('BBB'))
)
)
)
)
OR
(
( extract('year' FROM datetime('AAA')) -
extract('year' FROM datetime('BBB')) < 0
)
AND
(
( extract('month' FROM datetime('AAA')) >
extract('month' FROM datetime('BBB'))
)
OR
(
( extract('month' FROM datetime('AAA')) =
extract('month' FROM datetime('BBB'))
)
AND
( extract('day' FROM datetime('AAA')) >
extract('day' FROM datetime('BBB'))
)
)
)
)
THEN
ABS(extract('year' FROM datetime('AAA')) -
extract('year' FROM datetime('BBB'))) -1
ELSE
ABS(extract('year' FROM datetime('AAA')) -
extract('year' FROM datetime('BBB')))
END;

Greg Sabino Mullane
greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200112202130

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8IoLtvJuQZxSWSsgRAvsDAKDn/IlVBryqsAAsFU2+Dufv0TnwCACfbnKM
cneqtsX4bpJGJ0hGAlGEOnc=
=VQ8u
-----END PGP SIGNATURE-----