An Oracle DATE always contains a time part. The client date format (NLS_DATE_FORMAT) controls what you see. It does not change the data.

I tested this on Oracle 23ai Free:

• TRUNC(start_date) sets the time to 00:00:00. The client still shows the time part.
• TO_DATE(start_date, 'YYYY-MM-DD') gives ORA-01830. Oracle first changes the DATE to text with the time part, and the format is too short for this text.

To show only the date, use one of these:

SELECT TO_CHAR(start_date, 'YYYY-MM-DD') FROM table1;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

To store the date with the time set to midnight, use TRUNC(start_date) in your INSERT ... SELECT.

Hi,

In Oracle, the DATE data type stores both the date and the time (hours, minutes, and seconds). Therefore, seeing a value such as 2026-10-29 16:30:00 does not necessarily mean that something is wrong with your INSERT statement. It may simply be how your SQL client displays the value.

There are two different solutions, depending on your requirements.

1. Display only the date

If you want to display the date in YYYY-MM-DD format without changing the stored data, use TO_CHAR in your SELECT statement:

SELECT TO_CHAR(start_date, 'YYYY-MM-DD') AS start_date
FROM table1;

This returns a formatted string rather than an Oracle DATE value.

2. Remove the time component when inserting

If you want to store the date with the time set to midnight, use TRUNC:

INSERT INTO table1 (start_date)
SELECT TRUNC(start_date)
FROM table2;

This preserves the date while setting the time to 00:00:00.

3. Change the display format for your session

If you want Oracle to display dates without the time component by default in your current session, you can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

Note that this changes the default display format for the session, not the stored data or the underlying DATE data type.

Why are you getting those errors?

• ORA-01830: The date format model ended before the entire input string was converted.
• ORA-01861: The literal does not match the specified date format.
• ORA-01722: An invalid number was encountered, potentially because a value was implicitly converted to a number.

The exact cause depends on the expression you used. Avoid applying TO_DATE to a value that is already an Oracle DATE; TO_DATE is intended to convert character strings into dates.

Recommendation: If your only concern is how the result appears when queried, start with TO_CHAR(start_date, 'YYYY-MM-DD'). You do not need to modify the stored data.

For more details, see the [Oracle SQL Language Reference — Data Types](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html).