Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Convert Day of week (DY) to actual date
"Richard Brust" <richard_brust_at_yahoo.com> a écrit dans le message de
news:8b15ae11.0408181845.1560f7aa_at_posting.google.com...
> What I thought it would do is give me the date of MON (or TUE, or WED)
> of *this* week.
>
> I did another test of this using month:
>
> SQL> select to_date('AUG', 'MM') from dual;
>
> TO_DATE('
> ---------
> 01-AUG-04
>
> So, if it works for MONTH, meaning it gives me month of current year,
> why wouldn't DAY, as in day-of-week, give me current week's date?
>
> I did read in the Oracle book (Complete Ref) that day of week needs to
> be a number, not 'spelled out', as it were. But, still, why for
> Month, and not Day...
>
>
>
> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:<4123a91b$0$28262$626a14ce_at_news.free.fr>...
> > "Richard Brust" <richard_brust_at_yahoo.com> a écrit dans le message de
> > news:8b15ae11.0408181025.e180a60_at_posting.google.com...
> > > We have a table (duh!) with columns that users have been entering day
> > > abbreviations - MON, TUE, WED, etc...
> > >
> > > I would like to convert these days to actual dates, so that WED would
> > > be 18-Aug-2004, or whatever format I end up specifying.
> > >
> > > I have currently tried:
> > >
> > > select to_date(s_day, 'DD')
> > >
> > > which returns:
> > >
> > > ERROR at line 1:
> > > ORA-01858: a non-numeric character was found where a numeric was
> > > expected
> > >
> > > Thanks for any assistance!!!
> >
> > What is the meaning of to_date('MON',...)? Which Monday?
With table t (col varchar2(3)) containing the abreviate day name:
SQL> select t.col,
2 next_day(sysdate 3 -decode(sign(to_number(to_char(sysdate,'D'))-d.nb),-1,0,7), 4 d.day) thedate 5 from t, 6 ( select to_number(to_char(sysdate+rownum,'D')) nb, 7 to_char(sysdate+rownum,'DY') abr, 8 to_char(sysdate+rownum,'DAY') day 9 from ( select 1 from dual group by cube (1,2,3) ) 10 where rownum <= 7 ) d
MON 08/16/2004 WED 08/18/2004 THU 08/19/2004 SAT 08/21/2004
4 rows selected.
-- Regards Michel CadotReceived on Thu Aug 19 2004 - 11:03:55 CDT