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!!!

study date functions and format specifications in the SQL Reference manual before you go any further -- don't rely on partial examples in Oracle how-to books

you'll find that oracle will not convert an arbitrary day-of-week into a date, because it does not make any assumptions about which Monday (or Tuesday, etc.) you're interested in

however, you'll find that you can use SYSDATE, date arithmetic and the NEXT_DAY function to return a date for a specific day of the week as of a certain starting date

to save us all time (but you better look it up to make sure you know how it works) you need something like:

next_day(trunc(sysdate)-7,day_of_week_column)

++ mcs Received on Thu Aug 19 2004 - 07:52:37 CDT