How do I insert date in YYYY-MM -DD HH24:MI:SS format in Oracle

CodePudding user response：

INSERT INTO TABLE (COL1, ...) VALUES (
    to_date('2001-12-30 17:27:59', 'YYYY-MM-DD HH24:MI:SS')
); 

You submit a string and the date format to TO_DATE, and then use that in your VALUES clause of a TABLE INSERT command.

Oracle stores it as a DATE, there is no stored format of the date. When you query it out, you have the ability to get it back in the format of your choice, or leave it to the database or session parameters to be your default date format.

CodePudding user response：

DATE is a type, the format is just a representation. You can set it as:

TO_DATE('2001-12-30 17:27:59', 'YYYY-MM-DD HH24:MI:SS')

and insert that in your date field.

CodePudding user response：

For the ISO format, you can simply use a timestamp literal:

insert into tablename (datecol) values (timestamp '2022-07-19 15:33:01');

For other formats, use to_date() or to_timestamp() with a format string (as @thatjeffsmith showed in their answer).

UPDATE As @mathguy pointed out in their comment, this will implicitly convert the TIMESTAMP to a DATE. To make the conversion explicit, use

insert into tablename(datecol) values (cast (timestamp '2022-07-19 15:33:01' as date));