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

Home -> Community -> Usenet -> c.d.o.server -> Re: Convert Day of week (DY) to actual date

Re: Convert Day of week (DY) to actual date

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 19 Aug 2004 18:03:55 +0200
Message-ID: <4124cf30$0$8963$636a15ce@news.free.fr>

"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

 11 where t.col = d.abr
 12 order by 2
 13 /
COL THEDATE
--- ----------
MON 08/16/2004
WED 08/18/2004
THU 08/19/2004
SAT 08/21/2004

4 rows selected.

-- 
Regards
Michel Cadot
Received on Thu Aug 19 2004 - 11:03:55 CDT

Original text of this message

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