如何修改Oracle SQL语句以仅插入星期几及时分至日期列？

Great question! Let's break this down because Oracle's DATE type has an important quirk you need to account for here.

Oracle's DATE datatype always stores a full date and time (year, month, day, hour, minute, second)—you can't have a DATE value that only contains a day of the week and time of day. So instead of "inserting only those parts", we need to build a DATE value where the year/month/day are fixed (so they don't interfere with our day/time logic), and the day of week and time match exactly what you want.

Your original query uses TO_DATE('FRIDAY 15:00','DAY HH24:MI'), which will automatically fill in the year/month/day with values based on the current date (e.g., if you run it on a Wednesday, it'll use this week's Friday; run it on a Saturday, it'll use next week's Friday). To make this consistent and effectively "store only the day of week and time", here's what you need to modify:

Key Changes to Your Query #

Replace the dynamic TO_DATE call with a DATE value built from a fixed base date, adjusted to your target day of week and time. Here are two reliable approaches:

Approach 1: Fixed Base Date + Intervals

Pick a base date where you know the day of week (e.g., 1900-01-01 is a Monday). Then add days to reach your target day, plus an interval for the desired time:

INSERT INTO SALA_MATERIA(SALA_ID, MATERIA_ID, HORARIO)
VALUES (
    1,
    'PT',
    -- Base date: 1900-01-01 (Monday)
    -- Add 4 days to get to Friday, plus 15 hours and 0 minutes
    TRUNC(TO_DATE('1900-01-01', 'YYYY-MM-DD')) + INTERVAL '4' DAY + INTERVAL '15:00' HOUR TO MINUTE
);

Approach 2: Explicit Fixed Full Date

If you know a specific date that matches your target day of week (e.g., 1900-01-05 is a Friday), define the full date-time directly:

INSERT INTO SALA_MATERIA(SALA_ID, MATERIA_ID, HORARIO)
VALUES (
    1,
    'PT',
    TO_DATE('1900-01-05 15:00', 'YYYY-MM-DD HH24:MI')
);

Retrieving the Day/Time Later #

When you need to pull just the day of week and time from the HORARIO column, use TO_CHAR to format the value:

SELECT TO_CHAR(HORARIO, 'DAY HH24:MI') AS schedule_day_time
FROM SALA_MATERIA;

内容的提问来源于stack exchange，提问作者Hedgar Bezerra