Question

First time using Oracle SQL (I'm used to MySQL). I'm finding conflicting info on what the default date format is. After several attempts having to use TO_DATE with my INSERT INTO my table statements, I finally found the database I'm using expects DD-MON-YY (i.e. 25-JAN-18). Yet on various pages here in stackoverflow and elsewhere, I see some that say default is YYYYMMDD or DD/MM/YYYY or YYYY-MM-DD. Why so many conflicting pieces of information?

Answer 1

Oracle  database allows you to set the default format. Out of the box, the format is (typically) DD-MON-RR, where "RR" refers to a two-digit year. This is a pretty lousy format, from the perspective of ambiguity (two digit year?) and internationalization (for what countries is that actually the default?). But Oracle has been around a long, long time.

 

In another format(Standard)  are also defined by ISO, the International Standards Organization. They settled on something more like YYYY-MM-DD. Actually, the hyphens are optional, but I think they make the date much more readable.

 

Oracle accepts constants in this format, if you use DATE:

 

select DATE '2021-02-11'