sysdate sql question

Hi,

Does anyone know of a way if all you have is the current date (ie, sysdate), how to figure out when the last date was that was a Sunday?

I'm trying to write a week-to-date report that creates a dynamic constraint in a where cluase so that at any given time, the data shown is only the data from the most recent Sunday to the current date. So in the where clause I would have something like: where date_field >= <some kind of expression involving sysdate.

Right now, I can get it to work if I maintain a calendar table in the database that basically just contains every single date for some period of time (say, 5 years).

In that case, I would do something like this to create this dynamic constraint:

select blah, blah, blah from foo where date_field >= (select max ( day ) from calendar_table where
  to_char(day,'D')=1) and date_field <= sysdate

However, I'm wondering if anyone knows of an easier expression using just sysdate that could give me the same result without having to maintain this additional table.

in pseudo code the algorithm would be:

select
...

from
...

where
...

date_field >= (sysdate - (the date of the most recent sunday)) and date_field <= sysdate

Hope that makes sense and I appreciate any help. Gavin

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 24 2001 - 21:16:01 CST