How to check the date format for a specific column in a table in oracle? Does oracle DATE datatype have the ability to store both formats (US and european)? and if so, is there a way to distinguish which one is the month and which is the date?

CodePudding user response：

In Oracle, a DATE is a binary data type consisting of 7 bytes representing each of

1. century;
2. year-of-century;
3. month;
4. day;
5. hour;
6. minute; and
7. second

It ALWAYS has those components and it is NEVER stored in any particular format.

How to check the date format for a specific column in a table in oracle?

It does not have any format so you cannot.

If you want to see the binary values of a DATE you can use the DUMP function:

SELECT DUMP(date_column) FROM table_name;

Does oracle DATE datatype have the ability to store both formats (US and european)?

No, it never stores any format.

If you want to store formatted data then use a string data-type; however, this is generally considered to be bad practice for dates as it introduces ambiguity between days and months if you use both DD/MM/YYYY and MM/DD/YYYY. Best practice would be to store dates as a DATE and then pick the appropriate format when you DISPLAY the date.

If you want to DISPLAY a DATE date type then you can use the TO_CHAR function and specify the format model appropriate to the territory you want to display it as.

SELECT TO_CHAR(date_column, 'DD/MM/YYYY HH24:MI:SS') AS eu_format,
       TO_CHAR(date_column, 'MM/DD/YYYY HH24:MI:SS') AS us_format,
       TO_CHAR(date_column, 'YYYY-MM-DD"T"HH24:MI:SS') AS iso8601_format
FROM   table_name

db<>fiddle here

The default format model (as used by the SQL engine when implicitly converting strings-to-dates, and vice-versa, or used by some client applications to DISPLAY a date) for different territories is listed in this answer.

CodePudding user response：

Oracle dates are stored in an internal binary data format. Display formatting is handled by the client and is not stored.

This is the same as for numbers, for example, where the same value can be displayed as 1000, 1,000.00, 1e3 etc depending on the format mask used with the to_char function.