Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sysdate sql question

Re: sysdate sql question

From: Jining Han <hanj_at_mailcity.com>
Date: Thu, 25 Jan 2001 03:34:58 GMT
Message-ID: <94o6t2$fq1$1@nnrp1.deja.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US