Re: sysdate sql question

Excellent! This is exactly what I needed. Thanks for all the responses.

I have used the trunc function many times over the years to strip off the time component of a date, but I didn't realize it could be used in this manner.

Many thanks,
Gavin
In article <3A6FAD2D.E9725F60_at_rationalconcepts.com>,   "C. Ferguson" <c_ferguson_at_rationalconcepts.com> wrote:
> 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/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 00:54:09 CST