Aage,

> I had hoped that the DateDiff function would be useful to me, but it
> works in way that surprised me a little.
> E.g.
> select datediff(month,cast('30.06.2000' as date),
> cast('01.07.2000' as date))
> from rdb$database
> returns 1 (month),
> and
> select datediff(year,cast('30.12.2000' as date),
> cast('02.01.2002' as date))
> from rdb$database
> returns 2 (years).
>
> I understand what happens, but I had hoped for a function that would
> return 0 months (i.e. less than 1 month) and 1 year (i.e. "1 <=
> result < 2"). What I really need is to find "age" expressed as
> "completed years" between two dates.

It seems that this is another example where the SQL committee is not as
logical as your and I would be.

Accordingly to a MSDN page on DATEDIFF, where

Date1 = '2005-12-31 23:59:59.9999999'
Date2 = '2006-01-01 00:00:00.0000000'

** Note the different is only .0000001!!!


SELECT DATEDIFF(year, Date1, Date2);
SELECT DATEDIFF(quarter, Date1, Date2);
SELECT DATEDIFF(month, Date1, Date2);
SELECT DATEDIFF(day, Date1, Date2);
SELECT DATEDIFF(hour, Date1, Date2);
SELECT DATEDIFF(minute, Date1, Date2);

All of the queries would return a result == 1.

> I believe that one upon a time I had a set of procedures for working
> with dates, but I can no longer find it - or I may be misremembering
> (maybe it is in a long retired pc).

We create 2 UDFs which return the "age" between 2 dates, in days and
months (year is simply 12 months), just for this purpose.


Sean