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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Newbie - SQL question

Re: Oracle Newbie - SQL question

From: Jack Straw <jkstraw_no_spam__at_hotmail.com>
Date: Wed, 17 Jul 2002 19:12:51 GMT
Message-ID: <TkjZ8.2508$95.40557@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

"TurkBear" <jgreco1_at_mn.rr.com> wrote in message news:1hdbjuoc3sh6a4ibncvkq032l4slkphd9f_at_4ax.com...
> The 'd' is the format mask that returns the Number of the day ( Sunday=1,
Monday=2, etc)
>
> Today ( when I posted this) is Wednesday so:
> ------------------------------
> SQL> select to_char(sysdate,'d') from dual;
> T
> -
> 4
> ---------------------------------------
> Wednesday = 4 ( the 'd' format gives this)
>
>
>
>
> "Jack Straw" <jkstraw_no_spam__at_hotmail.com> wrote:
>
> >Hi Daniel,
> >
> >Thank for you response. Your answer seems to make a bit more sense to me
> >(or at least jive with my understanding of how the query executes)
> >
> >A couple question though (please correct me if I am making wrong
> >assumptions):
> >
> >1. If by default Oracle says sunday =1 monday =2...etc for the purposes
of
> >ordering by date.
> >
> >How does Sunday -1 =Monday? Isn't that like 1-1=2?
> >
> >2. What does the 'd' represent.
> >
> >Sorry if I am asking a dumb question here :-) Thanks again!
> >
> >jon
> >
> >
> >
> >"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> >news:3D35A407.6B60BFCF_at_exesolutions.com...
> >> Jack Straw wrote:
> >>
> >> > Hey everyone,
> >> >
> >> > Just getting my feet wet with Oracle and I have run into a question I
> >was
> >> > hoping to get some help with.
> >> >
> >> > I am currently studying for the Oracle SQL cert and came accross this
> >> > question:
> >> >
> >> > "Display the last name, hire date and the day of the week on which
the
> >> > employee started. Label the column DAY. ORder the results by the
day
> >of
> >> > the week starting with Monday"
> >> >
> >> > My question concerns the answer supplied in the text:
> >> >
> >> > "SELECT last_name, hire_date, TO_CHAR(hire_date,'fmDay')
> >> > FROM employees
> >> > ORDER by TO_CHAR(hire_date -1,'d')"
> >> >
> >> > I don't understand the ORDER BY clause.
> >> >
> >> > Specifically:
> >> >
> >> > 1) "hire_date -1"
> >> > - My understanding is that Oracle's day 1 of a week is Sunday. It
> >doesn't
> >> > seem intuative to use "-1" to move a day forward? Am I
misunderstanding
> >> > something?
> >> >
> >> > 2) 'd'
> >> > - What does this represent? I had assumed "day" but that
substitution
> >> > results in 'some other' weird grouping I don't understand. (ie. it
> >groups
> >> > all the days of the week but in no apparent order)
> >> >
> >> > Thanks in advance for any help!
> >> >
> >> > Jon
> >>
> >> It moves the value of Monday to 1 and the value of Sunday to 7.
> >>
> >> So the sort becomes Monday, Tuesday, .... Saturday, Sunday
> >>
> >> Daniel Morgan
> >>
> >
>
>
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=----- Received on Wed Jul 17 2002 - 14:12:51 CDT

Original text of this message

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