Re: sysdate sql question

The round and trunc functions make interesting reading but the note on them is that the first day of the week is dependent upon the NLS_TERRITORY setting.

Anyhow, here in PST land

select blah from table where mydate >= (select trunc(sysdate, 'D') from dual);

hth,
cindy

Jining Han wrote:

> 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 - 22:31:36 CST