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> wrote in message
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!!!
Richard
Never having converted a day into a date I thought I'd investigate its
behaviour.
I believe the SQL you're looking for is
select to_date(s_day, 'DY')
So I tried it.
SQL> select to_date('SAT', 'DY') from dual; select to_date('SAT', 'DY') from dual
*
ERROR at line 1:
ORA-01835: day of week conflicts with Julian date
Strange..
After some further investigation I found that changing SAT to SUN I get
SQL> select to_date('SUN', 'DY') from dual;
TO_DATE('
SQL> select SYSDATE from dual;
SYSDATE
My database :
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
on Windows 2000 SP4
I also get the same behaviour on another database on a different machine.
Other database :
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
on Red Hat 9 (thanks HJR).
Anybody else get this ?
Any clues to this behaviour ?
eric
-- Remove the dross to contact me directlyReceived on Thu Aug 19 2004 - 08:05:14 CDT