At 06:07 PM 21/01/2008, you wrote:

>Greetings, all. Suppose I have a table of time periods which I want to
>order by their length. I am trying to do something like the following,
>which I know does not work:
>
>SELECT P.PeriodName, (P.EndDate - P.StartDate) AS PeriodLength
>FROM Periods P
>ORDER BY 2 DESC

It should work. Have you tried? It won't be scintillatingly fast, of course, since ordering by expressions has no indexes to work with...but possibly the number of periods in the table is not huge.

>Is there any simple way to subtract dates in Firebird? I am not
>interested in the format (seconds, hours, minutes), as long as the
>records can be ordered by this value.

Hmm, are you under the impression that dates are stored as strings (or something?)

The (date2 - date1) calculation is as simple as it comes. If the dates are DATE type, the result is a 32-bit integer, representing days. If they are TIMESTAMP, the result is a DECIMAL(9,4) representing days + part-days.

If your two date fields are actually TIMESTAMP and you fear that your calcs on adjoining periods are getting messed up by the TIME part, you can CAST a timestamp as a date and get your plain integer for the interval result:

SELECT
P.PeriodName,
(CAST(P.EndDate as DATE) - CAST(P.StartDate as DATE)) AS PeriodLength
FROM Periods P
ORDER BY 2 DESC

./heLen