Oracle表单输入日期拆分存储至数据库日期列的实现咨询

Got it, let's work through this. The core thing to remember here is that Oracle's DATE datatype always stores a complete year-month-day (plus time) value—you can't store just a standalone year or month-day as a DATE. But we can work within this constraint by storing structured DATE values and formatting them to match your desired output when querying.

Why Your Previous Attempt Failed #

When you tried SUBSTR(TO_CHAR(TO_DATE(block.field)), you were overcomplicating things:

• block.field is already a DATE type, so wrapping it in TO_DATE() converts it to a string (using your session's default date format) and back to a DATE—leaving you with the original full date.
• When you stored that result into your DATE columns, Oracle just saved the full date, hence why both columns showed the complete 03-04-2018 value.

Step 1: Store the Date Parts as Valid DATE Values #

We need to construct valid DATE values for both columns:

• For the year column: Store the first day of the input date's year (e.g., 2018-01-01 for an input of 2018-03-04). This gives us a valid DATE that represents the year.
• For the day_month column: Store the month-day part with a fixed dummy year (I recommend 2000 since it's a leap year, which handles February 29 correctly). For example, 2000-03-04 for an input of 2018-03-04.

Here's how to implement this in a trigger (adjust table/field names to match yours):

CREATE OR REPLACE TRIGGER trg_populate_date_parts
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
DECLARE
  v_input_date DATE;
BEGIN
  -- If your form field is a text input, first convert it to DATE (adjust format mask to match user input)
  -- v_input_date := TO_DATE(:block.user_input_date, 'YYYY-MM-DD');
  
  -- If your form field is already a DATE type, use it directly:
  v_input_date := :block.user_input_date;

  -- Populate year column with the first day of the input year
  :NEW.year := TRUNC(v_input_date, 'YYYY');

  -- Populate day_month with month-day part using fixed year (2000)
  :NEW.day_month := TO_DATE(TO_CHAR(v_input_date, 'MM-DD'), 'MM-DD');
END;
/

Step 2: Query to Get Your Desired Format #

Now that the data is stored as valid DATEs, format them when querying to get the output you want:

SELECT
  TO_CHAR(year, 'YYYY') AS year,
  TO_CHAR(day_month, 'MM-DD') AS day_month
FROM your_table;

This query will return exactly the format you need:

year    day_month
----    ---------
2018    03-04

Key Notes #

• The dummy year (2000) in day_month doesn't affect your output because we're formatting it to only show month and day when querying.
• If your form uses a non-standard date input format, adjust the TO_DATE mask (e.g., 'DD-MM-YYYY' if users enter dates like 04-03-2018).

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