如何在Oracle表中插入日期？插入语句遇ORA-00904错误求助

Hey there! Let's tackle your Oracle issues one by one.

First: Resolving the ORA-00904 Error #

The ORA-00904: "HIREDATE": invalid identifier error means Oracle can't find a column named HIREDATE in your Works table. Here are the most common fixes:

• Verify your table structure: Run this command to check exactly what columns exist in Works:

  DESC Works;
  

  Double-check if the date column is named HireDate, hiredate, or something else entirely. Oracle defaults to uppercase column names unless you created the table with double quotes around the column name (e.g., "HireDate"). If you used quotes during table creation, you'll need to use the exact case with quotes in your INSERT statement:

  INSERT INTO Works (ClientID, CCode, BranchNo, EquipNo, "HireDate")
  -- rest of your query...
  

• Fix typos: Ensure you didn't misspell the column name in your INSERT statement (e.g., HireDt instead of HireDate).

Second: Correctly Inserting Dates in Oracle #

Using raw date strings like '23-JAN-13' is risky because it depends on your database's NLS_DATE_FORMAT setting. Instead, use these reliable methods:

1. Date Literals (Recommended) #

Oracle supports ANSI date literals in the format DATE 'YYYY-MM-DD'—this works regardless of your NLS settings:

INSERT INTO Works (ClientID, CCode, BranchNo, EquipNo, HIREDATE)
SELECT 001, 101, 01, 24500, DATE '2013-01-23' FROM DUAL
UNION
SELECT 002, 102, 01, 23200, DATE '2012-09-12' FROM DUAL
UNION
SELECT 003, 103, 01, 11500, DATE '2014-12-15' FROM DUAL
UNION
SELECT 004, 104, 01, 76830, DATE '2016-03-16' FROM DUAL
UNION
SELECT 005, 105, 01, 23760, DATE '2015-06-08' FROM DUAL;

2. TO_DATE Function #

Use TO_DATE() to explicitly define the format of your date string, so Oracle knows exactly how to parse it:

INSERT INTO Works (ClientID, CCode, BranchNo, EquipNo, HIREDATE)
SELECT 001, 101, 01, 24500, TO_DATE('23-JAN-13', 'DD-MON-RR') FROM DUAL
UNION
SELECT 002, 102, 01, 23200, TO_DATE('12-SEP-12', 'DD-MON-RR') FROM DUAL
UNION
SELECT 003, 103, 01, 11500, TO_DATE('15-DEC-14', 'DD-MON-RR') FROM DUAL
UNION
SELECT 004, 104, 01, 76830, TO_DATE('16-MAR-16', 'DD-MON-RR') FROM DUAL
UNION
SELECT 005, 105, 01, 23760, TO_DATE('08-JUN-15', 'DD-MON-RR') FROM DUAL;

The RR format mask handles two-digit years: values 00-69 map to 2000-2069, and 70-99 map to 1970-1999.

Quick Recap #

1. Confirm your Works table has the correct date column name with DESC Works;.
2. Use date literals or TO_DATE() for consistent, error-free date inserts.

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