Re: Oracle Newbie - SQL question

Jack you need to think "order of operations"

You are thinking that you subtract 1 from the day of the week, but that's not what the calculation does. The way you are thinking is   (to_char(sysdate,'d') - 1)

Bu the operation is
  to_char(sysdate -1,'d')

             You start with a date:   sysdate             eg,  July 23
                    subtract 1 day:  sysdate-1                 July 22
now get the day of the week number: to_char(sysdate-1, 'd')    1

I started with today (a Tuesday), so ended up with 1 consider a Sunday, July 21
subtract 1 to get July 20
get the day of the week, which is now 7(a Saturday)

Now do you see how this shifts the Sunday around to the end?

Jack Straw wrote:
>
> Can someone please read the history (in particular my last post in this
> thread and help :-)
>
> Thanks in advance!
>
> Jon
>
> "Jack Straw" <jkstraw_no_spam__at_hotmail.com> wrote in message
> news:TkjZ8.2508$95.40557_at_news...
> > Hi and thanks,
> >
> > So I modified the statement you provided to this:
> >
> > "select to_char(sysdate -1,'d') from dual"
> >
> > and as I expected it turned up:
> >
> > T
> > -
> > 3
> >
> > ie. Tuesday = 3
> >
> > This is why I am confused about my original question.
> >
> > to_char(hire_date,'d')
> >
> > returns
> >
> > Sunday
> > Monday
> > Tues
> > etc
> > etc
> >
> > But "to_char(hire_date -1, 'd')
> >
> > returns
> >
> > Monday
> > Tuesday
> > Wed
> > etc
> > etc
> >
> > I would have expected a result of
> >
> > Sat
> > Sun
> > Mon
> > etc
> > etc
> >
> > Am I nuts? <grin>
> >
> > jon
> >

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com