Lets assume that your value is days past 1899-12-31, then you can use:
INSERT ALL
INTO TABLE1 (NAME, DATE_COL) VALUES ('ABC' , DATE '1899-12-31' + 44370.4087037037 )
INTO TABLE1 (NAME, DATE_COL) VALUES ('XYZ' , DATE '1899-12-31' + 44370.4087384259 )
SELECT * FROM DUAL;
Then, for your table:
CREATE TABLE table1(
name VARCHAR2(10),
date_col DATE
);
The output would be:
SELECT * FROM table1;
Outputs (when NLS_DATE_FORMAT is YYYY-MM-DD HH24:MI:SS):
| NAME |
DATE_COL |
| ABC |
2021-06-24 09:48:32 |
| XYZ |
2021-06-24 09:48:35 |
Whereas I want them to be in MM/DD/YYYY HH:MM format.
A DATE column is stored as 7-bytes of binary data (1 byte each for century. year-of-century, month, day, hour, minute and second) and does NOT have a format.
If you want a formatted date then you need to use TO_CHAR in the SELECT statement to change it to a formatted string:
SELECT name, TO_CHAR( date_col, 'MM/DD/YYYY HH24:MI' ) AS date_str
FROM table1;
Which outputs:
| NAME |
DATE_STR |
| ABC |
06/24/2021 09:48 |
| XYZ |
06/24/2021 09:48 |
db<>fiddle here
If you are using Excel date values
From my answer here, there is a bug in Excel where it thinks 1900 is a leap year and the numeric value it assigns days is out by one. If you want to use Excel values then, assuming all your values are going to be after 1900-02-28 then you want to use 1899-12-30 as the epoch value and not 1899-12-31.
If you are going to have a mix of values from before and after 1900-02-28 then you can use a CASE expression as-per the previously linked answer.
