Suppose I have a table with a date field: DatePaid and a number field: AmountPaid. I need to break data up into weeks that take into account the system week start day. The EXTRACT(DAYOFWEEK is great but assumes a Monday week start. Can anyone think of a SQL only solution or am I stuck consolidating the data using a clientdataset?

Michael

>Suppose I have a table with a date field: DatePaid and a number field: AmountPaid. I need to break data up into weeks that take into account the system week start day. The EXTRACT(DAYOFWEEK is great but assumes a Monday week start. Can anyone think of a SQL only solution or am I stuck consolidating the data using a clientdataset?

I'm assuming you miss typed EXTRACT(DAYOFWEEK  and it should have been EXTRACT(WEEK, if so all you need is an offset to move the date you have back / forward by the difference between the system start date and Monday eg


select
_invoiced,
extract(WEEK from _invoiced),
extract(WEEK from _invoiced - INTERVAL '1' DAY) /* start the week on Sunday */
from invoices


not quite SQL only since the offset passed to INTERVAL has to be a constant so you have to build the SQL rather than passing in a parameter

Roy Lambert