Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Denmark
    Posts
    15

    Unanswered: SQL: ORDER BY on a weekday (column, that is!)

    I have a VARCHAR2 column named DAYOFWEEK which contains a day name ("monday" or "tuesday" etc...) and I want to sort by this column! How do I do this? TO_DATE() doesn't seem to accept VARCHAR2 as an argument.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, TO_DATE doesn't work with just a day of the week anyway:
    Code:
    SQL> select to_date('MONDAY','DAY') from dual;
    select to_date('MONDAY','DAY') from dual
                   *
    ERROR at line 1:
    ORA-01835: day of week conflicts with Julian date
    I guess 'MONDAY' is insufficient to identify a specific date, and Oracle doesn't choose to assume that you meant next Monday or last Monday or whatever.

    The only solution I can suggest right now is the DECODE:

    ORDER BY DECODE(dayofweek,'SUNDAY',1,'MONDAY',2,...)

  3. #3
    Join Date
    Mar 2003
    Location
    Denmark
    Posts
    15
    Works! Great! THX!

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just a suggestion, In the future, don't store "SUNDAY" or any other day of the week. Store the date and you can access the day of the week simply (and use it for sorting). For example if dayoftheweek had been a date, you could get the specific day of the week by using

    select to_char(DAYOFWEEK,'Day') FROM MY_TABLE;

    Would return 'Sunday' or 'Monday' or whatever.

    If you ever have date or time information, NEVER store it as a text string in the database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could also try:

    Code:
    select ... from tab1...
    order by instr('SUN,MON,TUE,WED,THU,FRI,SAT'
                      ,substr(DAYOFWEEK),1,3));
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •