Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sysdate sql question
In article <94o5pb$etc$1_at_nnrp1.deja.com>,
gdas_at_my-deja.com wrote:
> 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/
>
I am sure someone will have a better way to do this, but just to get going:
select sysdate - to_number(to_char(sysdate, 'IW')) + 1 from dual ;
-- Jining Han Sallie Mae Sent via Deja.com http://www.deja.com/Received on Wed Jan 24 2001 - 21:34:58 CST