Re: DAYOFWEEK Function

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0310220451.8a68332_at_posting.google.com... > "Anna C. Dent" <anacdent_at_hotmail.com> wrote in message news:<gGklb.79762$Ms2.41140_at_fed1read03>...
> > Alister Taylor wrote:
> >
> > > Dear All,
> > >
> > > I am having a little (trans: a LOT) of trouble geeting a DayofWeek
> > > function to work.
> > >
> > > Broadly, this is for a basic flight booking service, which stipulates
> > > that a given flight is to be made on a certain daynumber.
> > >
> > > A flight may be made on many dates, but must be on this daynumber. I
> > > think that the problem may lie in part with the date entered as ie
> > > '27/OCT/2003', but this seems not to be causing problems for other
> > > people within the group.
> >
> > Please note that Oracle is 'picky' when it comes to data types.
> >
> > '27/OCT/2003' is a STRING; just as is 'ABC'.
> >
> > You might want to look up the TO_DATE function.

>
> Alister, here is an example statement to help you out.
> UT1> l
>   1  select to_char(sysdate,'DDD') as DayOfYr,
>   2         to_char(sysdate,'D')   as DayOfWk,
>   3         to_char(sysdate,'YYYYMMDD') as ToDay
>   4* from sys.dual
> UT1> /
>
> DAY D TODAY
> --- - --------
> 295 4 20031022
>
>
> Anna mentioned looking up the to_date function but what you probably
> should look up is the list of "date format elements" via the index
> used in both the to_char and to_date functions rather than the
> functions themselves.  The information on the masks elements and the
> functions is usually separated by other material.
>
> HTH -- Mark D Powell --

Alister,

If you do use the to_char(date_value,'D') functionality as suggested by Mark, you need to be aware that the value it returns is affected by the NLS settings of the session that's executing the code ( something the Oracle documentation doesn't mention) e.g. Thursday is day 4 for nls_territory 'UNITED KINGDOM' and day 5 for 'AMERICA'.

Paul Dixon Received on Thu Oct 23 2003 - 04:27:42 CDT