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: Eric Parker <eric.parkerthedross_at_virgin.net>
Date: Thu, 19 Aug 2004 14:05:14 +0100
Message-ID: <2ojms9Fbad19U1@uni-berlin.de>

"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('



01-AUG-04 Just to clarify the situation I got SYSDATE

 SQL> select SYSDATE from dual;

SYSDATE



19-AUG-04 I was expecting the day to be converted into the date of the corresponding day
in this week. It appears to be converting it into the date of the corresponding day
in the first week of the current month. The first week of the month ending on the
first Sunday in the month. If the day happened to be in the previous month then
it errors ORA-01835. I get the same behaviour with numeric days and format string 'D' - to be expected I suppose.

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 directly
Received on Thu Aug 19 2004 - 08:05:14 CDT

Original text of this message

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