Subject Re: [ib-support] Date
Author Helen Borrie
At 02:33 PM 18/02/2003 +0530, you wrote:
>Hi There
>How to find out difference between two dates.
>My requirement is...
>A table(myTbl) has a date field(CloseDate).
>I want to select all columns from myTbl where CloseDate is more than 2 years

Do you mean "...all rows..."?

In dialect 3 (where a DATE is date-only)
select <fieldlist> from myTbl
where CloseDate < (CURRENT_DATE - (365 * 2))

In dialect 1 (where a DATE is like a dialect 3 TIMESTAMP):
select <fieldlist> from myTbl
where CloseDate < (cast('today' as date) - (365 * 2))
Don't use cast('now' as date) if you want to get all of the rows whose date
portion is older than two years, since the time portion will push the
"horizon" forward and cause the query to miss some.

Don't forget to subtract another day if there is a leap year in there.