"John Hay" wrote:

>>To make this work you can use CAST eg

>>SELECT SaleDate - CAST(EXTRACT(DAYOFWEEK FROM SaleDate)-1 AS INTERVAL DAY) AS StartOfWeek
FROM Sale

Ha! Seems so obvious now.


>>Once you sort the syntax (cast) it works really well (automatically ignores the nulls) eg

>>SELECT DATE'1901-01-01' + CAST(AVG(TransactionDate - DATE'1901-01-01') AS INTERVAL DAY) AS AverageDate FROM Table

DATE'1901-01-01' + AVG(TransactionDate - DATE'1901-01-01') works in 2.05B3 without any casts.

Chris

>
> DATE'1901-01-01' + AVG(TransactionDate - DATE'1901-01-01') works in 2.05B3 without any casts.
>

Maybe if I should read your post more carefully I could take my foot out of my mouth

John

Chris,

<< Anyway, the query for a date average that I wrote above actually works
fine ... I'm not sure why though ... AVG() appears to return an integer and
normally we're unable to add an integer to a date. >>

The query that you posted subtracts a date from a date, which will return a
DAY interval, by default.  The AVG() calculation is averaging days, not
normal integer values.   The two happen to be the same thing in this case,
but that's just a coincidence due to the unit of measure being used.

--
Tim Young
Elevate Software
www.elevatesoft.com