There are three different date presentation formats in Oracle Database 12c Release 2 (12.2). These are standard, short, and long dates. Example 9-7 illustrates the difference between the short data and long date formats for both United States and Germany.
Example 9-7 Difference Between Date Formats by Locale (United States and Germany)
SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;
Session altered.
SQL> SELECT employee_id EmpID,
2 SUBSTR(first_name,1,1)||'.'||last_name "EmpName",
3 TO_CHAR(hire_date,'DS') "Hiredate",
4 TO_CHAR(hire_date,'DL') "Long HireDate"
5 FROM employees
6* WHERE employee_id <105;
EMPID EmpName Hiredate Long HireDate
---------- --------------------------- ---------- -----------------------------
100 S.King 06/17/1987 Wednesday, June 17, 1987
101 N.Kochhar 09/21/1989 Thursday, September 21, 1989
102 L.De Haan 01/13/1993 Wednesday, January 13, 1993
103 A.Hunold 01/03/1990 Wednesday, January 3, 1990
104 B.Ernst 05/21/1991 Tuesday, May 21, 1991
SQL> ALTER SESSION SET SET NLS_TERRITORY=germany NLS_LANGUAGE=german;
Session altered.
SQL> SELECT employee_id EmpID,
2 SUBSTR(first_name,1,1)||'.'||last_name "EmpName",
3 TO_CHAR(hire_date,'DS') "Hiredate",
4 TO_CHAR(hire_date,'DL') "Long HireDate"
5 FROM employees
6* WHERE employee_id <105;
EMPID EmpName Hiredate Long HireDate
---------- --------------------------- -------- ------------------------------
100 S.King 17.06.87 Mittwoch, 17. Juni 1987
101 N.Kochhar 21.09.89 Donnerstag, 21. September 1989
102 L.De Haan 13.01.93 Mittwoch, 13. Januar 1993
103 A.Hunold 03.01.90 Mittwoch, 3. Januar 1990
104 B.Ernst 21.05.91 Dienstag, 21. Mai 1991