Oracle SQL中如何将DY格式星期缩写转换为对应星期数字？

Hey there! Let's dive into this problem using the weekly shift dataset we're working with. First, here's the sample shift data we're referencing:

with weekly_shifts(shift_date,start_time,end_time) as (
    SELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
    UNION ALL 
    SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
    UNION ALL 
    SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
    UNION ALL 
    SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
    UNION ALL 
    SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
)

The Problem #

We only have 3-letter day abbreviations (like MON, TUE, WED) in the shift_date column, and we need to convert these to their corresponding numeric day-of-week values (e.g., 2 for Monday, 3 for Tuesday, etc.).

Your Solution (And Why It Works) #

The approach you came up with using next_day() is a great, straightforward way to handle this in Oracle. Here's the query formatted cleanly:

select 
    to_char(next_day(sysdate, shift_date),'D') SHIFT_NUM, 
    weekly_shifts.* 
from weekly_shifts

Let me break down the logic:

• next_day(sysdate, shift_date): This function finds the next occurrence of the day specified in shift_date relative to the current date (sysdate). For example, if today is Wednesday, next_day(sysdate, 'MON') would return the upcoming Monday.
• to_char(..., 'D'): The 'D' format mask extracts the numeric day-of-week from the date. In Oracle's default setup, this returns 1 for Sunday, 2 for Monday, 3 for Tuesday, and so on up to 7 for Saturday—exactly the numbering we need here.

A Quick NLS Note #

If you're working in an environment where the default date language might not match your day abbreviations (e.g., non-English sessions), you can explicitly set the language in the to_char function to avoid mismatches:

select 
    to_char(
        next_day(sysdate, shift_date), 
        'D', 
        'NLS_DATE_LANGUAGE=ENGLISH'
    ) SHIFT_NUM, 
    weekly_shifts.* 
from weekly_shifts

This ensures the function correctly interprets English day abbreviations regardless of session settings.

内容的提问来源于stack exchange，提问作者Patrick H