Re: sysdate sql question

select TRUNC(sysdate,'DAY') from dual;

<gdas_at_my-deja.com> wrote in message news:94o5pb$etc$1_at_nnrp1.deja.com...
> 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 - 22:46:06 CST