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, ...
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