Ask the Expert

Sorting by weekday as text

I have a table with one field which is VARCHAR2 datatype containing weekdays randomly like this:
FRIDAY
MONDAY
SATURDAY
SUNDAY
THURSDAY
TUESDAY
WEDNESDAY

I want to fetch data in order in a single SQL statement without using any user-defined functions. Is there any function in Oracle that returns the weekday (as a number) for a particular day (a string)? That is, if I pass 'WEDNESDAY', then it returns 4, ...

    Requires Free Membership to View

Solution 1: The simplest and fastest way to do it is with CASE or DECODE:
SELECT DISTINCT
        dow_val,
        weekday
FROM    (
            SELECT  weekday,
                    DECODE ( UPPER (TRIM (weekday)),
                             'SUNDAY',    1,
                             'MONDAY',    2,
                             'TUESDAY',   3,
                             'WEDNESDAY', 4,
                             'THURSDAY',  5,
                             'FRIDAY',    6,
                             'SATURDAY',  7,
                                          NULL  -- None of the above
                           )  AS dow_val
            FROM    table_x
        )
ORDER BY
        dow_val;

   DOW_VAL WEEKDAY
---------- ---------
         1 SUNDAY
         2 MONDAY
         3 TUESDAY
         4 WEDNESDAY
         5 THURSDAY
         6 FRIDAY
         7 SATURDAY

7 rows selected.

Solution 2: We can reduce the typing (and the efficiency) by replacing the big DECODE statement with:

        TO_CHAR ( NEXT_DAY ( SYSDATE, weekday),
                  'D'
                )

TO_CHAR (dt, 'D') returns a single character, not a number, but it works just as well for sorting. The first argument to TO_CHAR must be a DATE, and all we have is a string; that's why we call NEXT_DAY (dt, wstring), which returns a date whose day-of-the-week is given in wstring.

Solution 3: Look how much cleaner your code would be if you were willing to use a user-defined function:

SELECT DISTINCT
        dow_val,
        weekday
FROM    (
            SELECT  weekday,
                    dow (weekday)  AS dow_val
            FROM    table_x
        )
ORDER BY
        dow_val;

This solution is nearly as efficient as Solution 1, and it's cleaner than Solution 2. The function isn't hard to write, and you only have to write it once. With either of the solutions above, you have to repeat the code in every statement where you need it.

CREATE OR REPLACE FUNCTION  dow
(
    in_weekday_text  IN  VARCHAR2
)
RETURN  PLS_INTEGER

--      *************
--      **  d o w  **
--      *************

--  dow returns a number (1-7) corresponding to the day of
--  the week passed in in_weekday_text.  Inputs do not
--  need to be capitalized, and may include leading or trailing
--  spaces, but they do have to be full words (not like 'MON.')
--  If in_weekday_text is not one of the seven weekday names,
--  dow returns NULL.

IS
BEGIN
    RETURN  CASE  UPPER (TRIM (in_weekday_text))
                WHEN   'SUNDAY'    THEN  1
                WHEN   'MONDAY'    THEN  2
                WHEN   'TUESDAY'   THEN  3
                WHEN   'WEDNESDAY' THEN  4
                WHEN   'THURSDAY'  THEN  5
                WHEN   'FRIDAY'    THEN  6
                WHEN   'SATURDAY'  THEN  7
                                   ELSE  NULL
            END;
END;
/
SHOW ERRORS

GRANT  EXECUTE  ON  dow  TO  PUBLIC;

CREATE PUBLIC SYNONYM  dow  FOR  foo.dow;

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: